import { getSheetIndex, getRangetxt } from '../methods/get'
import {
  replaceHtml,
  getObjType,
  ABCatNum,
  numFormat,
  numfloatlen,
  showrightclickmenu,
  mouseclickposition,
} from '../utils/util'
import {
  getdatabyselectionD,
  getcellvalue,
  datagridgrowth,
} from '../global/getdata'
import {
  isdatetime,
  diff,
  isdatatypemulti,
  isdatatype,
} from '../global/datecontroll'
import { genarate, update } from '../global/format'
import { isRealNull, isEditMode } from '../global/validate'
import { orderbydata1D } from '../global/sort'
import { jfrefreshgrid, jfrefreshgridall } from '../global/refresh'
import tooltip from '../global/tooltip'
import editor from '../global/editor'
import cleargridelement from '../global/cleargridelement'
import luckysheetArray from '../global/array'
import analysis from '../global/analysis'
import { selectHightlightShow } from './select'
import { luckysheet_searcharray } from './sheetSearch'
import {
  modelHTML,
  filtermenuHTML,
  filtersubmenuHTML,
  pivottableconfigHTML,
  pivottablesumHTML,
  luckysheetPivotTableHTML,
} from './constant'
import sheetmanage from './sheetmanage'
import luckysheetsizeauto from './resize'
import server from './server'
import { checkProtectionAuthorityNormal } from './protection'
import Store from '../store'
import locale from '../locale/locale'
import numeral from 'numeral'
import { luckysheetlodingHTML } from '../controllers/constant'

const pivotTable = {
  pivotDatas: null,
  pivotSheetIndex: 0,
  pivotDataSheetIndex: 0,
  celldata: null,
  origindata: null,
  getCellData: function(cursheetindex, datasheetindex, data_select_save) {
    let _this = this

    let sheetIndex
    if (cursheetindex != null) {
      sheetIndex = cursheetindex
    } else {
      sheetIndex = Store.currentSheetIndex
    }

    let realIndex = getSheetIndex(sheetIndex)

    if (getObjType(Store.luckysheetfile[realIndex].pivotTable) != 'object') {
      Store.luckysheetfile[realIndex].pivotTable = new Function(
        'return ' + Store.luckysheetfile[realIndex].pivotTable
      )()
    }

    if (Store.luckysheetfile[realIndex].pivotTable != null) {
      _this.column = Store.luckysheetfile[realIndex].pivotTable.column
      _this.row = Store.luckysheetfile[realIndex].pivotTable.row
      _this.values = Store.luckysheetfile[realIndex].pivotTable.values
      _this.filter = Store.luckysheetfile[realIndex].pivotTable.filter
      _this.showType = Store.luckysheetfile[realIndex].pivotTable.showType

      _this.filterparm = Store.luckysheetfile[realIndex].pivotTable.filterparm

      if (Store.luckysheetfile[realIndex].pivotTable.drawPivotTable != null) {
        _this.drawPivotTable =
          Store.luckysheetfile[realIndex].pivotTable.drawPivotTable
      } else {
        _this.drawPivotTable = true
      }

      if (
        Store.luckysheetfile[realIndex].pivotTable.pivotTableBoundary != null
      ) {
        _this.pivotTableBoundary =
          Store.luckysheetfile[realIndex].pivotTable.pivotTableBoundary
      } else {
        _this.pivotTableBoundary = [12, 6]
      }

      if (data_select_save != null) {
        _this.pivot_select_save = data_select_save
      } else {
        _this.pivot_select_save =
          Store.luckysheetfile[realIndex].pivotTable.pivot_select_save
      }

      if (datasheetindex != null) {
        _this.pivotDataSheetIndex = datasheetindex
      } else {
        _this.pivotDataSheetIndex =
          Store.luckysheetfile[realIndex].pivotTable.pivotDataSheetIndex
      }
    } else {
      _this.column = null
      _this.row = null
      _this.values = null
      _this.filter = null
      _this.showType = null

      _this.filterparm = null

      _this.drawPivotTable = true
      _this.pivotTableBoundary = [12, 6]

      if (data_select_save != null) {
        _this.pivot_select_save = data_select_save
      } else {
        _this.pivot_select_save = Store.luckysheet_select_save
      }

      if (datasheetindex != null) {
        _this.pivotDataSheetIndex = datasheetindex
      } else {
        _this.pivotDataSheetIndex = sheetIndex
      }
    }

    let pivotrealIndex = getSheetIndex(_this.pivotDataSheetIndex)

    let otherfile = Store.luckysheetfile[pivotrealIndex]
    if (otherfile['data'] == null) {
      otherfile['data'] = sheetmanage.buildGridData(otherfile)
    }
    _this.origindata = getdatabyselectionD(
      otherfile.data,
      _this.pivot_select_save
    )

    let rowhidden = {}
    if (_this.filterparm != null) {
      for (let f in _this.filterparm) {
        // 目的是取出rowhidden
        for (let h in _this.filterparm[f]) {
          if (h === 'rowhidden' && _this.filterparm[f][h] != null) {
            rowhidden = $.extend(true, rowhidden, _this.filterparm[f][h])
          }
        }
      }
    }
    _this.rowhidden = rowhidden

    _this.pivotSheetIndex = sheetIndex

    let newdata = []
    for (let i = 0; i < _this.origindata.length; i++) {
      if (_this.rowhidden != null && _this.rowhidden[i] != null) {
        continue
      }
      newdata.push([].concat(_this.origindata[i]))
    }
    _this.celldata = newdata

    _this.pivot_data_type = {}
    for (let c = 0; c < _this.celldata[1].length; c++) {
      let type = isdatatype(_this.celldata[1][c])
      _this.pivot_data_type[c.toString()] = type
    }
  },
  pivot_data_type: {},
  pivot_select_save: null,
  column: null,
  row: null,
  values: null,
  filter: null,
  showType: null,
  rowhidden: null,
  selected: null,
  caljs: null,
  initial: true,
  filterparm: null,
  luckysheet_pivotTable_select_state: false,
  jgridCurrentPivotInput: null,
  movestate: false,
  moveitemposition: [],
  movesave: {},
  showvaluecolrow: function() {
    let _this = this

    if (
      $(
        '#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item'
      ).length >= 2
    ) {
      $('#luckysheetpivottablevaluecolrowshow').show()

      if (_this.showType == 'column') {
        $('#luckysheetpivottablevaluecolrow').prop('checked', true)
        $(
          "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']"
        ).addClass('ui-state-active')

        $('#luckysheetpivottablevaluecolrow1').prop('checked', false)
        $(
          "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']"
        ).removeClass('ui-state-active')
      } else {
        $('#luckysheetpivottablevaluecolrow1').prop('checked', true)
        $(
          "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']"
        ).addClass('ui-state-active')

        $('#luckysheetpivottablevaluecolrow').prop('checked', false)
        $(
          "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']"
        ).removeClass('ui-state-active')
      }
    } else {
      $('#luckysheetpivottablevaluecolrowshow').hide()
    }
  },
  resetOrderby: function(obj) {
    let orderby = $(
      '#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item'
    ).index(obj)
    $(
      '#luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column'
    )
      .find('.luckysheet-modal-dialog-slider-config-item')
      .each(function() {
        if ($(this).data('orderby') == orderby) {
          $(this).data('orderby', 'self')
        }
      })
  },
  luckysheetsliderlistclearfilter: function($filter) {
    let _this = this

    let $t = $filter.parent()
    let cindex = $t.data('index')

    let rowhidden = {},
      selected = {},
      d = _this.origindata,
      filterdata = {}

    $t.data('rowhidden', '')
      .find('.luckysheet-slider-list-item-filtered')
      .hide()
    _this.setDatatojsfile('selected', {}, cindex)
    _this.setDatatojsfile('rowhidden', null, cindex)

    let newdata = []
    for (let i = 0; i < d.length; i++) {
      if (rowhidden[i] != null) {
        continue
      }
      newdata.push([].concat(d[i]))
    }

    _this.celldata = newdata
    _this.refreshPivotTable()
    $(
      '#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu'
    ).hide()
  },
  luckysheetsliderlistitemfilter: function($filter) {
    let _this = this

    const _locale = locale()
    const locale_filter = _locale.filter

    let $t = $filter.parent(),
      toffset = $t.offset(),
      $menu = $('#luckysheet-pivotTableFilter-menu'),
      winH = $(window).height(),
      winW = $(window).width()

    let cindex = $t.data('index')

    let rowhidden = $t.data('rowhidden')
    if (rowhidden == null || rowhidden == '') {
      rowhidden = {}
    } else if (getObjType(rowhidden) == 'string') {
      rowhidden = JSON.parse(rowhidden)
    }

    $('body .luckysheet-cols-menu').hide()
    $(
      '#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu'
    ).hide()
    $('#luckysheet-pivotTableFilter-byvalue-input').val('')
    $('#luckysheet-pivotTableFilter-bycondition')
      .next()
      .hide()
    $('#luckysheet-pivotTableFilter-byvalue')
      .next()
      .show()

    $menu.data('index', cindex)

    $(
      '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input'
    )
      .hide()
      .find('input')
      .val()
    $('#luckysheet-pivotTableFilter-selected span')
      .data('type', '0')
      .data('type', null)
      .text(locale_filter.filiterInputNone)

    let byconditiontype = $t.data('byconditiontype')
    $('#luckysheet-pivotTableFilter-selected span')
      .data('value', $t.data('byconditionvalue'))
      .data('type', byconditiontype)
      .text($t.data('byconditiontext'))

    if (byconditiontype == '2') {
      let $input = $(
        '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input2'
      )
        .show()
        .find('input')
      $input.eq(0).val($t.data('byconditionvalue1'))
      $input.eq(1).val($t.data('byconditionvalue2'))
    } else if (byconditiontype == '1') {
      $(
        '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input'
      )
        .eq(0)
        .show()
        .find('input')
        .val($t.data('byconditionvalue1'))
    }
    const loadingObj = luckysheetlodingHTML(
      '#luckysheet-pivotTableFilter-byvalue-select',
      { text: locale_filter.filiterMoreDataTip }
    )
    $('#luckysheet-pivotTableFilter-byvalue-select')
      .empty()
      .append(loadingObj.el)

    let rowhiddenother = {} //其它筛选列的隐藏行
    $(
      '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
    )
      .not($t.get(0))
      .each(function() {
        let $t = $(this),
          rh = $t.data('rowhidden')

        if (rh == null || rh == '') {
          return true
        }

        if (getObjType(rh) == 'string') {
          rh = JSON.parse(rh)
        }

        for (let r in rh) {
          rowhiddenother[r] = 0
        }
      })

    let data = _this.origindata

    setTimeout(function() {
      //日期值
      let dvmap = {}
      let dvmap_uncheck = {}

      //除日期以外的值
      let vmap = {}
      let vmap_uncheck = {}

      for (let r = 1; r < data.length; r++) {
        if (r in rowhiddenother) {
          continue
        }

        if (data[r] == null) {
          continue
        }

        let cell = data[r][cindex]

        if (cell != null && cell.ct != null && cell.ct.t == 'd') {
          //单元格是日期
          let v = update('YYYY-MM-DD', cell.v)

          let y = v.split('-')[0]
          let m = v.split('-')[1]
          let d = v.split('-')[2]

          if (!(y in dvmap)) {
            dvmap[y] = {}
          }

          if (!(m in dvmap[y])) {
            dvmap[y][m] = {}
          }

          if (!(d in dvmap[y][m])) {
            dvmap[y][m][d] = 0
          }

          dvmap[y][m][d]++

          if (r in rowhidden) {
            dvmap_uncheck[y] = 0
            dvmap_uncheck[m] = 0
            dvmap_uncheck[d] = 0
          }
        } else {
          let v, m
          if (cell == null || isRealNull(cell.v)) {
            v = null
            m = null
          } else {
            v = cell.v
            m = cell.m
          }

          if (!(v in vmap)) {
            vmap[v] = {}
          }

          if (!(m in vmap[v])) {
            vmap[v][m] = 0
          }

          vmap[v][m]++

          if (r in rowhidden) {
            vmap_uncheck[v + '#$$$#' + m] = 0
          }
        }
      }

      //遍历数据加到页面
      let item = []

      if (JSON.stringify(dvmap).length > 2) {
        for (let y in dvmap) {
          let ysum = 0
          let monthHtml = ''

          for (let m in dvmap[y]) {
            let msum = 0
            let dayHtml = ''

            for (let d in dvmap[y][m]) {
              let dayL = dvmap[y][m][d]
              msum += dayL

              //月 小于 10
              let mT
              if (Number(m) < 10) {
                mT = '0' + Number(m)
              } else {
                mT = m
              }

              //日 小于 10
              let dT
              if (Number(d) < 10) {
                dT = '0' + Number(d)
              } else {
                dT = d
              }

              //日是否选中状态
              if (
                y in dvmap_uncheck &&
                m in dvmap_uncheck &&
                d in dvmap_uncheck
              ) {
                dayHtml +=
                  '<div class="day luckysheet-mousedown-cancel cf" data-check="false" title="' +
                  y +
                  '-' +
                  mT +
                  '-' +
                  dT +
                  '">' +
                  '<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
                  '<label class="luckysheet-mousedown-cancel">' +
                  d +
                  '</label>' +
                  '<span class="count luckysheet-mousedown-cancel">( ' +
                  dayL +
                  ' )</span>' +
                  '</div>'
              } else {
                dayHtml +=
                  '<div class="day luckysheet-mousedown-cancel cf" data-check="true" title="' +
                  y +
                  '-' +
                  mT +
                  '-' +
                  dT +
                  '">' +
                  '<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
                  '<label class="luckysheet-mousedown-cancel">' +
                  d +
                  '</label>' +
                  '<span class="count luckysheet-mousedown-cancel">( ' +
                  dayL +
                  ' )</span>' +
                  '</div>'
              }
            }

            ysum += msum

            //月 小于 10
            let mT2
            if (Number(m) < 10) {
              mT2 = '0' + Number(m)
            } else {
              mT2 = m
            }

            //月是否选中状态
            if (y in dvmap_uncheck && m in dvmap_uncheck) {
              monthHtml +=
                '<div class="monthBox luckysheet-mousedown-cancel">' +
                '<div class="month luckysheet-mousedown-cancel cf" data-check="false" title="' +
                y +
                '-' +
                mT2 +
                '">' +
                '<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
                '<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
                '<label class="luckysheet-mousedown-cancel">' +
                m +
                '' +
                locale_filter.filiterMonthText +
                '</label>' +
                '<span class="count luckysheet-mousedown-cancel">( ' +
                msum +
                ' )</span>' +
                '</div>' +
                '<div class="dayList luckysheet-mousedown-cancel">' +
                dayHtml +
                '</div>' +
                '</div>'
            } else {
              monthHtml +=
                '<div class="monthBox luckysheet-mousedown-cancel">' +
                '<div class="month luckysheet-mousedown-cancel cf" data-check="true" title="' +
                y +
                '-' +
                mT2 +
                '">' +
                '<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
                '<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
                '<label class="luckysheet-mousedown-cancel">' +
                m +
                '' +
                locale_filter.filiterMonthText +
                '</label>' +
                '<span class="count luckysheet-mousedown-cancel">( ' +
                msum +
                ' )</span>' +
                '</div>' +
                '<div class="dayList luckysheet-mousedown-cancel">' +
                dayHtml +
                '</div>' +
                '</div>'
            }
          }

          //年是否选中状态
          let yearHtml
          if (y in dvmap_uncheck) {
            yearHtml =
              '<div class="yearBox luckysheet-mousedown-cancel">' +
              '<div class="year luckysheet-mousedown-cancel cf" data-check="false" title="' +
              y +
              '">' +
              '<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
              '<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
              '<label class="luckysheet-mousedown-cancel">' +
              y +
              '' +
              locale_filter.filiterYearText +
              '</label>' +
              '<span class="count luckysheet-mousedown-cancel">( ' +
              ysum +
              ' )</span>' +
              '</div>' +
              '<div class="monthList luckysheet-mousedown-cancel">' +
              monthHtml +
              '</div>' +
              '</div>'
          } else {
            yearHtml =
              '<div class="yearBox luckysheet-mousedown-cancel">' +
              '<div class="year luckysheet-mousedown-cancel cf" data-check="true" title="' +
              y +
              '">' +
              '<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
              '<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
              '<label class="luckysheet-mousedown-cancel">' +
              y +
              '' +
              locale_filter.filiterYearText +
              '</label>' +
              '<span class="count luckysheet-mousedown-cancel">( ' +
              ysum +
              ' )</span>' +
              '</div>' +
              '<div class="monthList luckysheet-mousedown-cancel">' +
              monthHtml +
              '</div>' +
              '</div>'
          }

          item.unshift(yearHtml)
        }
      }

      if (JSON.stringify(vmap).length > 2) {
        let vmapKeys = Object.keys(vmap)
        vmapKeys = orderbydata1D(vmapKeys, true)

        for (let i = 0; i < vmapKeys.length; i++) {
          let v = vmapKeys[i]

          for (let x in vmap[v]) {
            let text
            if (v + '#$$$#' + x == 'null#$$$#null') {
              text = locale_filter.valueBlank
            } else {
              text = x
            }

            //是否选中状态
            let dataHtml
            if (v + '#$$$#' + x in vmap_uncheck) {
              dataHtml =
                '<div class="textBox luckysheet-mousedown-cancel cf" data-check="false" data-filter="' +
                (v + '#$$$#' + x) +
                '" title="' +
                x +
                '">' +
                '<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
                '<label class="luckysheet-mousedown-cancel">' +
                text +
                '</label>' +
                '<span class="luckysheet-mousedown-cancel count">( ' +
                vmap[v][x] +
                ' )</span>' +
                '</div>'
            } else {
              dataHtml =
                '<div class="textBox luckysheet-mousedown-cancel cf" data-check="true" data-filter="' +
                (v + '#$$$#' + x) +
                '" title="' +
                x +
                '">' +
                '<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
                '<label class="luckysheet-mousedown-cancel">' +
                text +
                '</label>' +
                '<span class="luckysheet-mousedown-cancel count">( ' +
                vmap[v][x] +
                ' )</span>' +
                '</div>'
            }

            item.push(dataHtml)
          }
        }
      }

      // 适配小屏设备
      let containerH = winH - toffset.top - 350
      if (containerH < 0) containerH = 100
      //$("#luckysheet-pivotTableFilter-byvalue-select").html("<div class='ListBox luckysheet-mousedown-cancel' style='max-height:" + containerH + "px;overflow-y:auto;overflow-x:hidden;'>" + item.join("") + "</div>");

      $('#luckysheet-pivotTableFilter-byvalue-select').append(
        "<div class='ListBox luckysheet-mousedown-cancel' style='max-height:" +
          containerH +
          "px;overflow-y:auto;overflow-x:hidden;'>" +
          item.join('') +
          '</div>'
      )
      loadingObj.close()
    }, 1)

    showrightclickmenu($menu, toffset.left - 250, toffset.top)
  },
  getSumTypeName: function(type) {
    let name = ''

    const _locale = locale()
    const locale_pivotTable = _locale.pivotTable

    if (type == 'SUM') {
      name = locale_pivotTable.valueStatisticsSUM
    } else if (type == 'COUNT') {
      name = locale_pivotTable.valueStatisticsCOUNT
    } else if (type == 'COUNTA') {
      name = locale_pivotTable.valueStatisticsCOUNTA
    } else if (type == 'COUNTUNIQUE') {
      name = locale_pivotTable.valueStatisticsCOUNTUNIQUE
    } else if (type == 'AVERAGE') {
      name = locale_pivotTable.valueStatisticsAVERAGE
    } else if (type == 'MAX') {
      name = locale_pivotTable.valueStatisticsMAX
    } else if (type == 'MIN') {
      name = locale_pivotTable.valueStatisticsMIN
    } else if (type == 'MEDIAN') {
      name = locale_pivotTable.valueStatisticsMEDIAN
    } else if (type == 'PRODUCT') {
      name = locale_pivotTable.valueStatisticsPRODUCT
    } else if (type == 'STDEV') {
      name = locale_pivotTable.valueStatisticsSTDEV
    } else if (type == 'STDEVP') {
      name = locale_pivotTable.valueStatisticsSTDEVP
    } else if (type == 'let') {
      name = locale_pivotTable.valueStatisticslet
    } else if (type == 'VARP') {
      name = locale_pivotTable.valueStatisticsVARP
    }

    return name
  },
  setDatatojsfile: function(attr, value, cindex) {
    let _this = this

    let index = getSheetIndex(_this.pivotSheetIndex)
    if (Store.luckysheetfile[index]['pivotTable'] == null) {
      Store.luckysheetfile[index]['pivotTable'] = {}
    }

    if (cindex == null) {
      Store.luckysheetfile[index]['pivotTable'][attr] = value
      _this[attr] = value
    } else {
      if (Store.luckysheetfile[index]['pivotTable']['filterparm'] == null) {
        Store.luckysheetfile[index]['pivotTable']['filterparm'] = {}
      }

      if (
        Store.luckysheetfile[index]['pivotTable']['filterparm'][
          cindex.toString()
        ] == null
      ) {
        Store.luckysheetfile[index]['pivotTable']['filterparm'][
          cindex.toString()
        ] = {}
      }
      Store.luckysheetfile[index]['pivotTable']['filterparm'][
        cindex.toString()
      ][attr] = value

      if (_this['filterparm'] == null) {
        _this['filterparm'] = {}
      }

      if (_this['filterparm'][cindex.toString()] == null) {
        _this['filterparm'][cindex.toString()] = {}
      }

      _this['filterparm'][cindex.toString()][attr] = value
    }
  },
  createPivotTable: function(e) {
    if (isEditMode() || Store.allowEdit === false) {
      return
    }
    let _this = this

    let datasheetindex = Store.currentSheetIndex

    const _locale = locale()
    const locale_pivotTable = _locale.pivotTable

    if (isEditMode()) {
      alert(locale_pivotTable.errorNotAllowEdit)
      return
    }

    if (Store.luckysheet_select_save.length > 1) {
      tooltip.info('', locale_pivotTable.errorNotAllowMulti)
      return
    }

    if (
      Store.luckysheet_select_save.length == 0 ||
      Store.luckysheet_select_save[0].row[0] ==
        Store.luckysheet_select_save[0].row[1] ||
      Store.luckysheet_select_save[0].column[0] ==
        Store.luckysheet_select_save[0].column[1]
    ) {
      tooltip.info('', locale_pivotTable.errorSelectRange)
      return
    }

    let select_save = $.extend(true, {}, Store.luckysheet_select_save[0])
    sheetmanage.addNewSheet(e, true)

    _this.getCellData(Store.currentSheetIndex, datasheetindex, select_save)

    _this.setDatatojsfile('pivot_select_save', select_save)
    _this.setDatatojsfile('pivotDataSheetIndex', datasheetindex)

    _this.initialPivotManage()
  },
  changePivotTable: function(index) {
    let _this = this

    const _locale = locale()
    const locale_pivotTable = _locale.pivotTable

    let pivotDataSheetIndex =
      Store.luckysheetfile[getSheetIndex(index)].pivotTable.pivotDataSheetIndex
    let real_pivotDataSheetIndex = getSheetIndex(pivotDataSheetIndex)

    if (real_pivotDataSheetIndex == null) {
      tooltip.info(locale_pivotTable.errorIsDamage, '')
      return
    }

    _this.getCellData(index)
    _this.initialPivotManage(true)
    _this.refreshPivotTable() //初始化在一个普通sheet页，从此普通sheet页切换到数据透视表页时，需要刷新下数据，否则还是旧数据
  },
  refreshPivotTable: function(isRefreshCanvas = true) {
    let _this = this

    let redo = {}
    redo['pivotTable'] = pivotTable
    redo['data'] = editor.deepCopyFlowData(Store.flowdata) //取数据

    _this.storePivotTableParam()
    let ret = _this.dataHandler(
      _this.column,
      _this.row,
      _this.values,
      _this.showType,
      _this.celldata
    )
    _this.setDatatojsfile('pivotDatas', ret)

    let d = $.extend(true, [], sheetmanage.nulldata)
    let data = d

    let addr = 0,
      addc = 0

    if (ret.length == 0) {
      _this.setDatatojsfile('drawPivotTable', true)
      _this.setDatatojsfile('pivotTableBoundary', [12, 6])
    } else {
      _this.setDatatojsfile('drawPivotTable', false)
      _this.setDatatojsfile('pivotTableBoundary', [ret.length, ret[0].length])

      let rlen = ret.length,
        clen = ret[0].length

      addr = rlen - d.length
      addc = clen - d[0].length

      data = datagridgrowth(d, addr + 20, addc + 10, true)

      for (let r = 0; r < rlen; r++) {
        let x = [].concat(data[r])
        for (let c = 0; c < clen; c++) {
          let value = ''
          if (ret[r] != null && ret[r][c] != null) {
            value = getcellvalue(r, c, ret)
          }
          x[c] = value
        }
        data[r] = x
      }
    }

    redo['type'] = 'pivotTable_change'
    redo['curdata'] = $.extend(true, [], data)
    redo['sheetIndex'] = Store.currentSheetIndex
    redo['pivotTablecur'] = _this.getPivotTableData()

    if (Store.clearjfundo) {
      Store.jfundo.length = 0
      Store.jfredo.push(redo)
    }

    cleargridelement()
    Store.clearjfundo = false

    if (addr > 0 || addc > 0) {
      jfrefreshgridall(
        data[0].length,
        data.length,
        data,
        null,
        Store.luckysheet_select_save,
        'datachangeAll',
        undefined,
        undefined,
        isRefreshCanvas
      )
    } else {
      jfrefreshgrid(
        data,
        Store.luckysheet_select_save,
        {},
        null,
        isRefreshCanvas
      )
      selectHightlightShow()
    }

    Store.clearjfundo = true
  },
  drawPivotTable: true,
  pivotTableBoundary: [12, 6],
  pivotclick: function(row_index, col_index, index) {
    if (index == null) {
      index = Store.currentSheetIndex
    }

    let file = Store.luckysheetfile[getSheetIndex(index)]

    if (!file.isPivotTable) {
      return
    }

    let pivotDataSheetIndex = file.pivotTable.pivotDataSheetIndex
    let real_pivotDataSheetIndex = getSheetIndex(pivotDataSheetIndex)

    if (real_pivotDataSheetIndex == null) {
      return
    }

    let slider = $('#luckysheet-modal-dialog-slider-pivot')

    let isRangeClick = this.isPivotRange(row_index, col_index)
    if (isRangeClick && slider.is(':hidden')) {
      if (
        !checkProtectionAuthorityNormal(index, 'usePivotTablereports', false)
      ) {
        // Store.luckysheet_select_status = false;
        return
      }
      slider.show()
      luckysheetsizeauto()
      $('#luckysheet-sta-content').css('padding-right', 260)
    } else if (!isRangeClick && slider.is(':visible')) {
      slider.hide()
      luckysheetsizeauto()
      $('#luckysheet-sta-content').css('padding-right', 10)
    }
  },
  isPivotRange: function(row_index, col_index) {
    let _this = this

    if (!!Store.luckysheetcurrentisPivotTable) {
      if (
        row_index < _this.pivotTableBoundary[0] &&
        col_index < _this.pivotTableBoundary[1]
      ) {
        return true
      } else {
        return false
      }
    }
  },
  storePivotTableParam: function() {
    let _this = this
    let columnarr = [],
      rowarr = [],
      filterarr = [],
      valuesarr = []

    $(
      '#luckysheet-modal-dialog-config-filter .luckysheet-modal-dialog-slider-config-item'
    ).each(function() {
      let item = {}
      item['index'] = $(this).data('index')
      item['name'] = $(this).data('name')
      item['fullname'] = $(this)
        .find('.luckysheet-modal-dialog-slider-config-item-txt')
        .text()
      filterarr.push(item)
    })

    $(
      '#luckysheet-modal-dialog-config-row .luckysheet-modal-dialog-slider-config-item'
    ).each(function() {
      let item = {}
      item['index'] = $(this).data('index')
      item['name'] = $(this).data('name')
      item['fullname'] = $(this)
        .find('.luckysheet-modal-dialog-slider-config-item-txt')
        .text()
      item['order'] = $(this).data('order')
      item['orderby'] = $(this).data('orderby')
      item['stastic'] = $(this).data('stastic')
      rowarr.push(item)
    })

    $(
      '#luckysheet-modal-dialog-config-column .luckysheet-modal-dialog-slider-config-item'
    ).each(function() {
      let item = {}
      item['index'] = $(this).data('index')
      item['name'] = $(this).data('name')
      item['fullname'] = $(this)
        .find('.luckysheet-modal-dialog-slider-config-item-txt')
        .text()
      item['order'] = $(this).data('order')
      item['orderby'] = $(this).data('orderby')
      item['stastic'] = $(this).data('stastic')
      columnarr.push(item)
    })

    $(
      '#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item'
    ).each(function() {
      let item = {}
      item['index'] = $(this).data('index')
      item['name'] = $(this).data('name')
      item['fullname'] = $(this)
        .find('.luckysheet-modal-dialog-slider-config-item-txt')
        .text()
      item['sumtype'] = $(this).data('sumtype')
      item['nameindex'] = $(this).data('nameindex')
      valuesarr.push(item)
    })

    _this.setDatatojsfile('column', columnarr)
    _this.setDatatojsfile('row', rowarr)
    _this.setDatatojsfile('filter', filterarr)
    _this.setDatatojsfile('values', valuesarr)
    let showtype = $(
      '#luckysheetpivottablevaluecolrow:checked, #luckysheetpivottablevaluecolrow1:checked'
    ).val()
    _this.setDatatojsfile('showType', showtype == '0' ? 'row' : 'column')

    let pivotTable = _this.getPivotTableData()
    delete pivotTable.pivotDatas
    server.saveParam('all', _this.pivotSheetIndex, pivotTable, {
      k: 'pivotTable',
    })
  },
  getPivotTableData: function(dataindex) {
    if (dataindex == null) {
      dataindex = this.pivotSheetIndex
    }

    let index = getSheetIndex(dataindex)
    let pivotTable = Store.luckysheetfile[index]['pivotTable']

    if (getObjType(pivotTable) == 'object') {
      pivotTable = $.extend(true, {}, Store.luckysheetfile[index]['pivotTable'])
    } else {
      pivotTable = new Function('return ' + pivotTable)()
    }

    return pivotTable
  },
  addValuesToTitle: function(titles, values) {
    let rowLen = titles.length * values.length,
      colLen = titles[0].length + 1

    let retdata = []
    if (titles.length == 0 && values.length > 0) {
      for (let v = 0; v < values.length; v++) {
        retdata.push(values[v].fullname)
      }

      return retdata
    }

    if (values.length == 0 && titles.length > 0) {
      return titles
    }

    for (let r = 0; r < rowLen; r++) {
      retdata[r] = new Array(colLen)

      for (let c = 0; c < colLen - 1; c++) {
        retdata[r][c] = titles[Math.floor(r / values.length)][c]
      }

      retdata[r][colLen - 1] = values[r % values.length].fullname
    }

    return retdata
  },
  initialPivotManage: function(restore) {
    let _this = this
    const _locale = locale()
    const locale_pivotTable = _locale.pivotTable
    const locale_button = _locale.button
    const locale_filter = _locale.filter

    if (_this.initial) {
      _this.initial = false

      $('body')
        .first()
        .append(luckysheetPivotTableHTML())
      $('#luckysheet-modal-dialog-slider-close').click(function() {
        $('#luckysheet-modal-dialog-slider-pivot').hide()
        luckysheetsizeauto()
      })

      $('body')
        .first()
        .append(
          replaceHtml(modelHTML, {
            id: 'luckysheet-data-pivotTable-selection',
            addclass: 'luckysheet-data-pivotTable-selection',
            title: locale_pivotTable.titleSelectionDataRange,
            content:
              '<input id="luckysheet-pivotTable-range-selection-input" class="luckysheet-datavisual-range-container" style="font-size: 14px;padding:5px;max-width:none;" spellcheck="false" aria-label="' +
              locale_pivotTable.titleDataRange +
              '" placeholder="' +
              locale_pivotTable.titleDataRange +
              '">',
            botton:
              '<button id="luckysheet-pivotTable-selection-confirm" class="btn btn-primary">' +
              locale_button.confirm +
              '</button><button class="btn btn-default luckysheet-model-close-btn">' +
              locale_button.cancel +
              '</button>',
          })
        )

      $('body')
        .first()
        .append(replaceHtml(filtermenuHTML(), { menuid: 'pivotTableFilter' }))
      $('body')
        .first()
        .append(
          replaceHtml(filtersubmenuHTML(), { menuid: 'pivotTableFilter' })
        )
      $('body')
        .first()
        .append(pivottableconfigHTML())
      $('body')
        .first()
        .append(pivottablesumHTML())

      $('#luckysheet-pivotTableFilter-orderby-asc').remove()
      $('#luckysheet-pivotTableFilter-orderby-desc')
        .next()
        .remove()
      $('#luckysheet-pivotTableFilter-orderby-desc').remove()
      $('#luckysheet-pivotTableFilter-orderby-color')
        .next()
        .remove()
      $('#luckysheet-pivotTableFilter-orderby-color').remove()

      $('#luckysheetpivottablevaluecolrow, #luckysheetpivottablevaluecolrow1')
        .checkboxradio({
          icon: false,
        })
        .change(function() {
          _this.refreshPivotTable()
        })

      let hidefilersubmenu = null
      $('#luckysheet-pivotTableFilter-menu').mouseover(function() {
        clearTimeout(hidefilersubmenu)
        hidefilersubmenu = setTimeout(function() {
          $('#luckysheet-pivotTableFilter-submenu').hide()
        }, 500)
      })

      //点击复选框
      $(document)
        .off('click.ptFilterCheckbox1')
        .on(
          'click.ptFilterCheckbox1',
          '#luckysheet-pivotTableFilter-byvalue-select .textBox',
          function() {
            if ($(this).attr('data-check') == 'true') {
              $(this).attr('data-check', 'false')
              $(this)
                .find("input[type='checkbox']")
                .removeAttr('checked')
            } else {
              $(this).attr('data-check', 'true')
              $(this)
                .find("input[type='checkbox']")
                .prop('checked', true)
            }
          }
        )
      $(document)
        .off('click.ptFilterCheckbox2')
        .on(
          'click.ptFilterCheckbox2',
          '#luckysheet-pivotTableFilter-byvalue-select .year',
          function() {
            if ($(this).attr('data-check') == 'true') {
              $(this).attr('data-check', 'false')
              $(this)
                .parents('.yearBox')
                .find('.month')
                .attr('data-check', 'false')
              $(this)
                .parents('.yearBox')
                .find('.day')
                .attr('data-check', 'false')
              $(this)
                .parents('.yearBox')
                .find("input[type='checkbox']")
                .removeAttr('checked')
            } else {
              $(this).attr('data-check', 'true')
              $(this)
                .parents('.yearBox')
                .find('.month')
                .attr('data-check', 'true')
              $(this)
                .parents('.yearBox')
                .find('.day')
                .attr('data-check', 'true')
              $(this)
                .parents('.yearBox')
                .find("input[type='checkbox']")
                .prop('checked', true)
            }
          }
        )
      $(document)
        .off('click.ptFilterCheckbox3')
        .on(
          'click.ptFilterCheckbox3',
          '#luckysheet-pivotTableFilter-byvalue-select .month',
          function() {
            //月份 对应的 天
            if ($(this).attr('data-check') == 'true') {
              $(this).attr('data-check', 'false')
              $(this)
                .parents('.monthBox')
                .find('.day')
                .attr('data-check', 'false')
              $(this)
                .parents('.monthBox')
                .find("input[type='checkbox']")
                .removeAttr('checked')
            } else {
              $(this).attr('data-check', 'true')
              $(this)
                .parents('.monthBox')
                .find('.day')
                .attr('data-check', 'true')
              $(this)
                .parents('.monthBox')
                .find("input[type='checkbox']")
                .prop('checked', true)
            }
            //月份 对应的 年份
            let yearDayAllCheck = true
            let $yearDay = $(this)
              .parents('.yearBox')
              .find('.day')
            $yearDay.each(function(i, e) {
              if ($(e).attr('data-check') == 'true') {
              } else {
                yearDayAllCheck = false
              }
            })
            if (yearDayAllCheck) {
              $(this)
                .parents('.yearBox')
                .find('.year')
                .attr('data-check', 'true')
              $(this)
                .parents('.yearBox')
                .find(".year input[type='checkbox']")
                .prop('checked', true)
            } else {
              $(this)
                .parents('.yearBox')
                .find('.year')
                .attr('data-check', 'false')
              $(this)
                .parents('.yearBox')
                .find(".year input[type='checkbox']")
                .removeAttr('checked')
            }
          }
        )
      $(document)
        .off('click.ptFilterCheckbox4')
        .on(
          'click.ptFilterCheckbox4',
          '#luckysheet-pivotTableFilter-byvalue-select .day',
          function() {
            if ($(this).attr('data-check') == 'true') {
              $(this).attr('data-check', 'false')
              $(this)
                .find("input[type='checkbox']")
                .removeAttr('checked')
            } else {
              $(this).attr('data-check', 'true')
              $(this)
                .find("input[type='checkbox']")
                .prop('checked', true)
            }
            //天 对应的 月份
            let monthDayAllCheck = true
            let $monthDay = $(this)
              .parents('.monthBox')
              .find('.day')
            $monthDay.each(function(i, e) {
              if ($(e).attr('data-check') == 'true') {
              } else {
                monthDayAllCheck = false
              }
            })
            if (monthDayAllCheck) {
              $(this)
                .parents('.monthBox')
                .find('.month')
                .attr('data-check', 'true')
              $(this)
                .parents('.monthBox')
                .find(".month input[type='checkbox']")
                .prop('checked', true)
            } else {
              $(this)
                .parents('.monthBox')
                .find('.month')
                .attr('data-check', 'false')
              $(this)
                .parents('.monthBox')
                .find(".month input[type='checkbox']")
                .removeAttr('checked')
            }
            //天 对应的 年份
            let yearDayAllCheck = true
            let $yearDay = $(this)
              .parents('.yearBox')
              .find('.day')
            $yearDay.each(function(i, e) {
              if ($(e).attr('data-check') == 'true') {
              } else {
                yearDayAllCheck = false
              }
            })
            if (yearDayAllCheck) {
              $(this)
                .parents('.yearBox')
                .find('.year')
                .attr('data-check', 'true')
              $(this)
                .parents('.yearBox')
                .find(".year input[type='checkbox']")
                .prop('checked', true)
            } else {
              $(this)
                .parents('.yearBox')
                .find('.year')
                .attr('data-check', 'false')
              $(this)
                .parents('.yearBox')
                .find(".year input[type='checkbox']")
                .removeAttr('checked')
            }
          }
        )

      //日期 三级下拉显示
      $(document)
        .off('click.ptFilterYearDropdown')
        .on(
          'click.ptFilterYearDropdown',
          '#luckysheet-pivotTableFilter-byvalue-select .yearBox .fa-caret-right',
          function() {
            let $p = $(this).parents('.luckysheet-mousedown-cancel')
            if ($p.hasClass('year')) {
              $(this)
                .parents('.yearBox')
                .find('.monthList')
                .slideToggle()
            }
            if ($p.hasClass('month')) {
              $(this)
                .parents('.monthBox')
                .find('.dayList')
                .slideToggle()
            }
          }
        )

      //全选
      $('#luckysheet-pivotTableFilter-byvalue-btn-all').click(function() {
        $(
          "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
        ).prop('checked', true)
        $(
          "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
        )
          .parents('.luckysheet-mousedown-cancel')
          .attr('data-check', 'true')
      })

      //反选
      $('#luckysheet-pivotTableFilter-byvalue-btn-contra').click(function() {
        let $input = $(
          "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
        )
        $input.each(function(i, e) {
          if ($(e).is(':checked')) {
            $(e).removeAttr('checked')
            $(e)
              .parents('.luckysheet-mousedown-cancel')
              .attr('data-check', 'false')
          } else {
            $(e).prop('checked', true)
            $(e)
              .parents('.luckysheet-mousedown-cancel')
              .attr('data-check', 'true')
          }
        })
        //天 对应的 月份
        let $month = $(
          '#luckysheet-pivotTableFilter-byvalue-select .ListBox .monthBox'
        )
        $month.each(function(index, event) {
          let monthDayAllCheck = true
          let $monthDay = $(event).find(".day input[type='checkbox']")
          $monthDay.each(function(i, e) {
            if ($(e).is(':checked')) {
            } else {
              monthDayAllCheck = false
            }
          })
          if (monthDayAllCheck) {
            $(event)
              .find(".month input[type='checkbox']")
              .prop('checked', true)
            $(event).attr('data-check', 'true')
          } else {
            $(event)
              .find(".month input[type='checkbox']")
              .removeAttr('checked')
            $(event).attr('data-check', 'false')
          }
        })
        //天 对应的 年份
        let $year = $(
          '#luckysheet-pivotTableFilter-byvalue-select .ListBox .yearBox'
        )
        $year.each(function(index, event) {
          let yearDayAllCheck = true
          let $yearDay = $(event).find(".day input[type='checkbox']")
          $yearDay.each(function(i, e) {
            if ($(e).is(':checked')) {
            } else {
              yearDayAllCheck = false
            }
          })
          if (yearDayAllCheck) {
            $(event)
              .find(".year input[type='checkbox']")
              .prop('checked', true)
            $(event).attr('data-check', 'true')
          } else {
            $(event)
              .find(".year input[type='checkbox']")
              .removeAttr('checked')
            $(event).attr('data-check', 'false')
          }
        })
      })

      //清除
      $('#luckysheet-pivotTableFilter-byvalue-btn-clear').click(function() {
        $(
          "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
        ).removeAttr('checked')
        $(
          "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
        )
          .parents('.luckysheet-mousedown-cancel')
          .attr('data-check', 'false')
      })

      //按照值进行筛选
      $('#luckysheet-pivotTableFilter-byvalue-input').on(
        'input propertychange',
        function() {
          let v = $(this)
            .val()
            .toString()
          $(
            '#luckysheet-pivotTableFilter-byvalue-select .ListBox .luckysheet-mousedown-cancel'
          ).show()
          if (v != '') {
            let $check = $(
              "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
            )
            $check.each(function(i, e) {
              let $p = $(e).parents('.luckysheet-mousedown-cancel')
              if ($p.hasClass('day')) {
                //日期
                let day = $(e)
                  .siblings('label')
                  .text()
                  .toString()
                let month = $(e)
                  .parents('.monthBox')
                  .find('.month label')
                  .text()
                  .toString()
                let year = $(e)
                  .parents('.yearBox')
                  .find('.year label')
                  .text()
                  .toString()
                let itemV = year + '-' + month + '-' + day

                if (itemV.indexOf(v) == -1) {
                  $(e)
                    .parents('.day')
                    .hide()
                  //天 对应的 月份
                  let $monthDay = $(e)
                    .parents('.dayList')
                    .find('.day:visible')
                  if ($monthDay.length == 0) {
                    $(e)
                      .parents('.monthBox')
                      .find('.month')
                      .hide()
                  }
                  //天 对应的 年份
                  let $yearDay = $(e)
                    .parents('.monthList')
                    .find('.day:visible')
                  if ($yearDay.length == 0) {
                    $(e)
                      .parents('.yearBox')
                      .find('.year')
                      .hide()
                  }
                }
              }
              if ($p.hasClass('textBox')) {
                //其它
                let itemV = $(e)
                  .siblings('label')
                  .text()
                  .toString()

                if (itemV.indexOf(v) == -1) {
                  $(e)
                    .parents('.textBox')
                    .hide()
                }
              }
            })
          }
        }
      )

      $(
        '#luckysheet-pivotTableFilter-bycondition, #luckysheet-pivotTableFilter-byvalue'
      ).click(function() {
        let $t = $(this)
        $t.next().slideToggle(200)
        setTimeout(function() {
          if (
            $t.attr('id') == 'luckysheet-pivotTableFilter-bycondition' &&
            $('#luckysheet-pivotTableFilter-bycondition')
              .next()
              .is(':visible')
          ) {
            if (
              $('#luckysheet-pivotTableFilter-selected span').text() !=
              locale_filter.filiterInputNone
            ) {
              $('#luckysheet-pivotTableFilter-byvalue')
                .next()
                .slideUp(200)
            }
          }

          if ($t.is($('#luckysheet-pivotTableFilter-bycondition'))) {
            if (
              $('#luckysheet-pivotTableFilter-bycondition')
                .next()
                .is(':hidden') &&
              $('#luckysheet-pivotTableFilter-byvalue')
                .next()
                .is(':hidden')
            ) {
              $('#luckysheet-pivotTableFilter-byvalue')
                .next()
                .slideDown(200)
            }
          }
        }, 300)
      })

      //取消按钮
      $('#luckysheet-pivotTableFilter-cancel').click(function() {
        $(
          '#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu'
        ).hide()
      })

      $('#luckysheet-pivotTableFilter-selected').click(function() {
        let $t = $(this),
          toffset = $t.offset(),
          $menu = $('#luckysheet-pivotTableFilter-submenu')
        $menu.hide()
        let winH = $(window).height(),
          winW = $(window).width()
        let menuW = $menu.width(),
          menuH = $menu.height()
        let top = toffset.top,
          left = toffset.left,
          mheight = winH - toffset.top - 20
        if (toffset.left + menuW > winW) {
          left = toffset.left - menuW
        }

        if (toffset.top > winH / 2) {
          top = winH - toffset.top
          if (top < 0) {
            top = 0
          }

          mheight = toffset.top - 20
        }

        $menu.css({ top: top, left: left, height: mheight }).show()
        clearTimeout(hidefilersubmenu)
      })

      //按条件过滤
      $('#luckysheet-pivotTableFilter-submenu')
        .mouseover(function() {
          clearTimeout(hidefilersubmenu)
        })
        .find('.luckysheet-cols-menuitem')
        .click(function(e) {
          $('#luckysheet-pivotTableFilter-selected span')
            .html(
              $(this)
                .find('.luckysheet-cols-menuitem-content')
                .text()
            )
            .data('value', $(this).data('value'))
          $(
            '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input'
          ).hide()
          if ($(this).data('type') == '2') {
            $('#luckysheet-pivotTableFilter-selected span').data('type', '2')
            $(
              '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input2'
            ).show()
          } else if ($(this).data('type') == '0') {
            $('#luckysheet-pivotTableFilter-selected span').data('type', '0')
          } else {
            $('#luckysheet-pivotTableFilter-selected span').data('type', '1')
            $(
              '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input'
            )
              .eq(0)
              .show()
            //若是日期 改变input type类型为date
            if (
              $(this).attr('data-value') == 'dateequal' ||
              $(this).attr('data-value') == 'datelessthan' ||
              $(this).attr('data-value') == 'datemorethan'
            ) {
              $(
                '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input input'
              ).prop('type', 'date')
            } else {
              $(
                '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input input'
              ).prop('type', 'text')
            }
          }
          $('#luckysheet-pivotTableFilter-byvalue')
            .next()
            .slideUp()
          $('#luckysheet-pivotTableFilter-submenu').hide()
        })

      $('#luckysheet-modal-dialog-pivotTable-list').on(
        'click',
        ' .luckysheet-slider-list-item-filter',
        function(e) {
          _this.luckysheetsliderlistitemfilter($(this))
          e.stopPropagation()
          return false
        }
      )

      $('#luckysheet-modal-dialog-pivotTable-list').on(
        'click',
        ' .luckysheet-slider-list-item-filtered',
        function(e) {
          _this.luckysheetsliderlistclearfilter($(this).next())
          e.stopPropagation()
          return false
        }
      )

      $('#luckysheet-dialog-pivotTable-range-seleted').click(function() {
        $('#luckysheet-modal-dialog-slider-pivot').hide()
        luckysheetsizeauto()
        let $t = $('#luckysheet-data-pivotTable-selection'),
          myh = $t.outerHeight(),
          myw = $t.outerWidth()
        let winw = $(window).width(),
          winh = $(window).height()
        let scrollLeft = $(document).scrollLeft(),
          scrollTop = $(document).scrollTop()

        $('#luckysheet-data-pivotTable-selection')
          .css({
            left: (winw + scrollLeft - myw) / 2,
            top: (winh + scrollTop - myh) / 4,
          })
          .show()

        _this.jgridCurrentPivotInput = $(
          '#luckysheet-dialog-pivotTable-range'
        ).html()
        $('#luckysheet-pivotTable-range-selection-input').val(
          _this.jgridCurrentPivotInput
        )
        _this.luckysheet_pivotTable_select_state = true
      })

      //清除筛选按钮
      $('#luckysheet-pivotTableFilter-initial').click(function() {
        $(
          '#luckysheet-modal-dialog-pivotTable-list .luckysheet-slider-list-item-filtered'
        ).hide()
        $(
          '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
        ).data('rowhidden', '')
        $(
          '#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu'
        ).hide()
        $(
          '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input'
        )
          .hide()
          .find('input')
          .val()
        $('#luckysheet-pivotTableFilter-selected span')
          .data('type', '0')
          .data('type', null)
          .text(locale_filter.filiterInputNone)

        _this.setDatatojsfile('filterparm', null)
        _this.celldata = _this.origindata

        _this.refreshPivotTable()
      })

      $(
        '#luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column'
      ).on(
        'click',
        '.luckysheet-modal-dialog-slider-config-item-icon',
        function(e) {
          let $t = $(e.target),
            $item = $t.closest('.luckysheet-modal-dialog-slider-config-item'),
            cindex = $item.data('index'),
            toffset = $item.offset()
          let order = $item.data('order'),
            orderby = $item.data('orderby'),
            stastic = $item.data('stastic')

          if (order == null) {
            order = 'default'
          }

          let option =
            '<option value="self">' +
            $item
              .find('.luckysheet-modal-dialog-slider-config-item-txt')
              .data('name') +
            '</option>'

          $(
            '#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item'
          ).each(function(i) {
            option +=
              '<option value="' +
              i +
              '">' +
              $(this)
                .find('.luckysheet-modal-dialog-slider-config-item-txt')
                .text() +
              '</option>'
          })
          $('#luckysheet-pivotTable-config-option-orderby')
            .empty()
            .html(option)

          if (orderby == null) {
            orderby = 'self'
          }

          if (stastic == null) {
            stastic = '1'
          }

          $('#luckysheet-pivotTable-config-option-order')
            .val(order)
            .data('index', cindex)
          $('#luckysheet-pivotTable-config-option-orderby')
            .val(orderby)
            .data('index', cindex)
          $('#luckysheet-pivotTable-config-option-stastic')
            .val(stastic)
            .data('index', cindex)

          mouseclickposition(
            $('#luckysheet-pivotTable-config-option'),
            toffset.left + $item.outerWidth(),
            toffset.top - 13,
            'rightbottom'
          )
          e.stopPropagation()
          return false
        }
      )

      $(
        '#luckysheet-pivotTable-config-option-order,#luckysheet-pivotTable-config-option-orderby,#luckysheet-pivotTable-config-option-stastic'
      ).change(function() {
        let $t = $(this),
          cindex = $t.data('index')

        $(
          '#luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column'
        )
          .find('.luckysheet-modal-dialog-slider-config-item')
          .each(function() {
            if ($(this).data('index') == cindex) {
              $(this).data(
                $t
                  .attr('id')
                  .replace('luckysheet-pivotTable-config-option-', ''),
                $t.val()
              )
            }
          })

        _this.refreshPivotTable()
      })

      $('#luckysheet-modal-dialog-config-value').on(
        'click',
        '.luckysheet-modal-dialog-slider-config-item-icon',
        function(e) {
          let $t = $(e.target),
            $item = $t.closest('.luckysheet-modal-dialog-slider-config-item'),
            cindex = $item.data('index'),
            toffset = $item.offset(),
            sumtype = $item.data('sumtype')

          let type = _this.pivot_data_type[cindex.toString()]
          if (sumtype == null) {
            if (type == 'num') {
              sumtype = 'SUM'
            } else {
              sumtype = 'COUNTA'
            }
          }

          let $menu = $('#luckysheet-pivotTable-config-option-sumtype')
          $menu.find('.luckysheet-submenu-arrow').hide()
          $menu
            .find(
              ".luckysheet-cols-menuitem[sumtype='" +
                sumtype +
                "'] .luckysheet-submenu-arrow"
            )
            .css('display', 'inline')
          $menu.data('item', $item)

          mouseclickposition(
            $menu,
            toffset.left + $item.outerWidth(),
            toffset.top - 13,
            'rightbottom'
          )
          e.stopPropagation()
          return false
        }
      )

      $(
        '#luckysheet-pivotTable-config-option-sumtype .luckysheet-cols-menuitem'
      ).click(function() {
        let $item = $('#luckysheet-pivotTable-config-option-sumtype').data(
          'item'
        )
        let sumtype = $(this).attr('sumtype')
        $item.data('sumtype', $(this).attr('sumtype'))
        let name = _this.getSumTypeName(sumtype) + ':' + $item.data('name')
        $item
          .attr('title', name)
          .find('.luckysheet-modal-dialog-slider-config-item-txt')
          .html(name)
        $('#luckysheet-pivotTable-config-option-sumtype').hide()
        _this.refreshPivotTable()
      })

      $('#luckysheet-modal-dialog-config-filter').on(
        'click',
        '.luckysheet-modal-dialog-slider-config-item-icon',
        function(e) {
          let $t = $(e.target),
            cindex = $t
              .closest('.luckysheet-modal-dialog-slider-config-item')
              .data('index')
          _this.luckysheetsliderlistitemfilter(
            $(
              '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
            )
              .eq(cindex)
              .find('.luckysheet-slider-list-item-filter')
          )
          e.stopPropagation()
          return false
        }
      )

      //确认按钮
      $('#luckysheet-pivotTableFilter-confirm').click(function() {
        let $menu = $('#luckysheet-pivotTableFilter-menu')
        let cindex = $menu.data('index')

        let rowhiddenother = {} //其它筛选列的隐藏行
        $(
          '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
        ).each(function() {
          let $t = $(this),
            rh = $t.data('rowhidden')

          if ($t.data('index') != cindex) {
            if (rh == null || rh == '') {
              return true
            }

            if (getObjType(rh) == 'string') {
              rh = JSON.parse(rh)
            }

            for (let r in rh) {
              rowhiddenother[r] = 0
            }
          }
        })

        let d = _this.origindata

        let filterdata = {}
        let rowhidden = {}
        let caljs = {}

        if (
          $('#luckysheet-pivotTableFilter-bycondition')
            .next()
            .is(':visible') &&
          $('#luckysheet-pivotTableFilter-byvalue')
            .next()
            .is(':hidden') &&
          $('#luckysheet-pivotTableFilter-selected span').data('value') !=
            'null'
        ) {
          let $t = $('#luckysheet-pivotTableFilter-selected span')
          let type = $t.data('type'),
            value = $t.data('value')

          caljs['value'] = value
          caljs['text'] = $t.text()

          if (type == '0') {
            caljs['type'] = '0'
          } else if (type == '2') {
            let $input = $(
              '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input2 input'
            )
            caljs['type'] = '2'
            caljs['value1'] = $input.eq(0).val()
            caljs['value2'] = $input.eq(1).val()
          } else {
            caljs['type'] = '1'
            caljs['value1'] = $(
              '#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input'
            )
              .eq(0)
              .find('input')
              .val()
          }

          for (let r = 1; r < d.length; r++) {
            if (r in rowhiddenother) {
              continue
            }

            if (d[r] == null) {
              continue
            }

            let cell = d[r][cindex]

            if (value == 'cellnull') {
              //单元格为空
              if (cell != null && !isRealNull(cell.v)) {
                rowhidden[r] = 0
              }
            } else if (value == 'cellnonull') {
              //单元格有数据
              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              }
            } else if (value == 'textinclude') {
              //文本包含
              let value1 = caljs['value1']

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else {
                if (cell.m.indexOf(value1) == -1) {
                  rowhidden[r] = 0
                }
              }
            } else if (value == 'textnotinclude') {
              //文本不包含
              let value1 = caljs['value1']

              if (cell == null || isRealNull(cell.v)) {
              } else {
                if (cell.m.indexOf(value1) > -1) {
                  rowhidden[r] = 0
                }
              }
            } else if (value == 'textstart') {
              //文本开头为
              let value1 = caljs['value1'],
                valuelen = value1.length

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else {
                if (cell.m.substr(0, valuelen) != value1) {
                  rowhidden[r] = 0
                }
              }
            } else if (value == 'textend') {
              //文本结尾为
              let value1 = caljs['value1'],
                valuelen = value1.length

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else {
                if (
                  valuelen > cell.m.length ||
                  cell.m.substr(cell.m.length - valuelen, valuelen) != value1
                ) {
                  rowhidden[r] = 0
                }
              }
            } else if (value == 'textequal') {
              //文本等于
              let value1 = caljs['value1']

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else {
                if (cell.m != value1) {
                  rowhidden[r] = 0
                }
              }
            } else if (value == 'dateequal') {
              //日期等于
              let value1 = genarate(caljs['value1'])[2]

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'd') {
                if (parseInt(cell.v) != value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'datelessthan') {
              //日期早于
              let value1 = genarate(caljs['value1'])[2]

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'd') {
                if (parseInt(cell.v) >= value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'datemorethan') {
              //日期晚于
              let value1 = genarate(caljs['value1'])[2]

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'd') {
                if (parseInt(cell.v) <= value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'morethan') {
              //大于
              let value1 = parseFloat(caljs['value1'])

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v <= value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'moreequalthan') {
              //大于等于
              let value1 = parseFloat(caljs['value1'])

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v < value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'lessthan') {
              //小于
              let value1 = parseFloat(caljs['value1'])

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v >= value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'lessequalthan') {
              //小于等于
              let value1 = parseFloat(caljs['value1'])

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v > value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'equal') {
              //等于
              let value1 = parseFloat(caljs['value1'])

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v != value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'noequal') {
              //不等于
              let value1 = parseFloat(caljs['value1'])

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v == value1) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'include') {
              //介于
              let value1 = parseFloat(caljs['value1']),
                value2 = parseFloat(caljs['value2'])

              let min, max
              if (value1 < value2) {
                min = value1
                max = value2
              } else {
                max = value1
                min = value2
              }

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v < min || cell.v > max) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            } else if (value == 'noinclude') {
              //不在其中
              let value1 = parseFloat(caljs['value1']),
                value2 = parseFloat(caljs['value2'])

              let min, max
              if (value1 < value2) {
                min = value1
                max = value2
              } else {
                max = value1
                min = value2
              }

              if (cell == null || isRealNull(cell.v)) {
                rowhidden[r] = 0
              } else if (cell.ct != null && cell.ct.t == 'n') {
                if (cell.v >= min && cell.v <= max) {
                  rowhidden[r] = 0
                }
              } else {
                rowhidden[r] = 0
              }
            }
          }
        } else {
          $(
            "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']"
          ).each(function(i, e) {
            if ($(e).is(':visible') && $(e).is(':checked')) {
              return true
            }

            if ($(e).closest('.day').length > 0) {
              let day = $(e)
                .siblings('label')
                .text()
              if (Number(day) < 10) {
                day = '0' + day
              }

              let month = $(e)
                .closest('.monthBox')
                .find('.month label')
                .text()
                .replace(locale_filter.filiterMonthText, '')
              if (Number(month) < 10) {
                month = '0' + month
              }

              let year = $(e)
                .closest('.yearBox')
                .find('.year label')
                .text()
                .replace(locale_filter.filiterYearText, '')

              let itemV =
                locale_filter.filterDateFormatTip +
                '#$$$#' +
                year +
                '-' +
                month +
                '-' +
                day

              filterdata[itemV] = '1'
            }

            if ($(e).closest('.textBox').length > 0) {
              let itemV = $(e)
                .closest('.textBox')
                .data('filter')

              filterdata[itemV] = '1'
            }
          })

          for (let r = 1; r < d.length; r++) {
            if (r in rowhiddenother) {
              continue
            }

            if (d[r] == null) {
              continue
            }

            let cell = d[r][cindex]

            let value
            if (cell == null || isRealNull(cell.v)) {
              value = 'null#$$$#null'
            } else if (cell.ct != null && cell.ct.t == 'd') {
              let fmt = update('YYYY-MM-DD', cell.v)
              value = locale_filter.filterDateFormatTip + '#$$$#' + fmt
            } else {
              value = cell.v + '#$$$#' + cell.m
            }

            if (value in filterdata) {
              rowhidden[r] = 0
            }
          }
        }

        let $top = $(
          '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
        ).eq(cindex)
        if (
          $(
            "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']:visible:checked"
          ).length <
            $(
              "#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']:visible"
            ).length ||
          $('#luckysheet-pivotTableFilter-byvalue-input').val().length > 0 ||
          ($('#luckysheet-pivotTableFilter-bycondition')
            .next()
            .is(':visible') &&
            $('#luckysheet-pivotTableFilter-byvalue')
              .next()
              .is(':hidden') &&
            $('#luckysheet-pivotTableFilter-selected span').data('value') !=
              'null')
        ) {
          $top
            .data('rowhidden', JSON.stringify(rowhidden))
            .find('.luckysheet-slider-list-item-filtered')
            .show()
          _this.setDatatojsfile('rowhidden', rowhidden, cindex)

          if (caljs != null) {
            $top
              .data('byconditionvalue', caljs['value'])
              .data('byconditiontype', caljs['type'])
              .data('byconditiontext', caljs['text'])

            if (caljs['value1'] != null) {
              $top.data('byconditionvalue1', caljs['value1'])
            }

            if (caljs['value2'] != null) {
              $top.data('byconditionvalue2', caljs['value2'])
            }

            _this.setDatatojsfile('caljs', caljs, cindex)
          }
        } else {
          $top
            .data('rowhidden', '')
            .find('.luckysheet-slider-list-item-filtered')
            .hide()
          _this.setDatatojsfile('rowhidden', null, cindex)
        }

        let newdata = []
        for (let i = 0; i < d.length; i++) {
          if (i in rowhidden || i in rowhiddenother) {
            continue
          }

          newdata.push([].concat(d[i]))
        }

        _this.celldata = newdata
        _this.refreshPivotTable()
        $(
          '#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu'
        ).hide()

        cleargridelement()
      })

      $(
        '#luckysheet-data-pivotTable-selection .luckysheet-model-close-btn, #luckysheet-data-pivotTable-selection .luckysheet-modal-dialog-title-close'
      ).click(function() {
        $('#luckysheet-modal-dialog-slider-pivot').show()
        luckysheetsizeauto()
        $(
          '#luckysheet-cell-main .luckysheet-pivotTable-selection-set div'
        ).show()

        $('#luckysheet-data-pivotTable-selection').hide()

        sheetmanage.changeSheetExec(_this.pivotSheetIndex)

        _this.luckysheet_pivotTable_select_state = false

        cleargridelement()
      })

      $('#luckysheet-pivotTable-selection-confirm').click(function() {
        let $input = $('#luckysheet-pivotTable-range-selection-input'),
          val = $input.val()

        if (
          $.trim(val).length == 0 ||
          $.trim(val).toUpperCase() ==
            _this.jgridCurrentPivotInput.toUpperCase()
        ) {
          $input.val(_this.jgridCurrentPivotInput)
          $(
            '#luckysheet-data-pivotTable-selection .luckysheet-model-close-btn'
          ).click()
          return
        } else {
          let val1 = val.split('!')
          let sheettxt = '',
            rangetxt = '',
            sheetIndex = -1

          if (val1.length > 1) {
            sheettxt = val1[0]
            rangetxt = val1[1]

            for (let i in Store.luckysheetfile) {
              if (sheettxt == Store.luckysheetfile[i].name) {
                sheetIndex = Store.luckysheetfile[i].index
                break
              }
            }

            if (sheetIndex == -1) {
              sheetIndex = 0
            }
          } else {
            let index = getSheetIndex(Store.currentSheetIndex)
            sheettxt = Store.luckysheetfile[index].name
            sheetIndex = Store.luckysheetfile[index].index
            rangetxt = val1[0]
          }

          if (Store.luckysheetfile[getSheetIndex(sheetIndex)].isPivotTable) {
            if (isEditMode()) {
              alert(locale_pivotTable.errorNotAllowPivotData)
            } else {
              tooltip.info('', locale_pivotTable.errorNotAllowPivotData)
            }
            $input.val(_this.jgridCurrentPivotInput)
            return
          }

          if (rangetxt.indexOf(':') == -1) {
            if (isEditMode()) {
              alert(locale_pivotTable.errorSelectionRange)
            } else {
              tooltip.info('', locale_pivotTable.errorSelectionRange)
            }
            $input.val(_this.jgridCurrentPivotInput)
            return
          }

          rangetxt = rangetxt.split(':')
          let row = [],
            col = []

          row[0] = parseInt(rangetxt[0].replace(/[^0-9]/g, '')) - 1
          row[1] = parseInt(rangetxt[1].replace(/[^0-9]/g, '')) - 1

          if (row[0] > row[1]) {
            if (isEditMode()) {
              alert(locale_pivotTable.errorSelectionRange)
            } else {
              tooltip.info('', locale_pivotTable.errorSelectionRange)
            }
            $input.val(_this.jgridCurrentPivotInput)
            return
          }

          col[0] = ABCatNum(rangetxt[0].replace(/[^A-Za-z]/g, ''))
          col[1] = ABCatNum(rangetxt[1].replace(/[^A-Za-z]/g, ''))

          if (col[0] > col[1]) {
            if (isEditMode()) {
              alert(locale_pivotTable.errorSelectionRange)
            } else {
              tooltip.info(locale_pivotTable.errorSelectionRange)
            }
            $input.val(_this.jgridCurrentPivotInput)
            return
          }
          sheetmanage.changeSheetExec(_this.pivotSheetIndex)

          _this.setDatatojsfile('pivot_select_save', { row: row, column: col })
          _this.setDatatojsfile('pivotDataSheetIndex', sheetIndex)

          _this.getCellData(_this.pivotSheetIndex, sheetIndex, {
            row: row,
            column: col,
          })

          _this.initialPivotManage()

          $('#luckysheet-dialog-pivotTable-range').html(val)

          $('#luckysheet-modal-dialog-slider-pivot').show()

          $('#luckysheet-data-pivotTable-selection').hide()

          _this.luckysheet_pivotTable_select_state = false

          _this.refreshPivotTable()

          luckysheetsizeauto()

          cleargridelement()
        }
      })

      $('#luckysheet-modal-dialog-slider-pivot').on(
        'mousedown',
        '.luckysheet-slider-list-item-name, .luckysheet-modal-dialog-slider-config-item-txt',
        function(e) {
          let $cur = $(e.target)
          _this.movestate = true
          _this.movesave.obj = $cur.parent()
          _this.movesave.name = $cur.data('name')
          _this.movesave.containerid = $cur
            .parent()
            .parent()
            .attr('id')
          _this.movesave.index = $cur.data('index')

          if ($('#luckysheet-modal-dialog-slider-pivot-move').length == 0) {
            $('body')
              .first()
              .append(
                '<div id="luckysheet-modal-dialog-slider-pivot-move">' +
                  _this.movesave.name +
                  '</div>'
              )
          }

          _this.movesave.width = $(
            '#luckysheet-modal-dialog-slider-pivot-move'
          ).outerWidth()
          _this.movesave.height = $(
            '#luckysheet-modal-dialog-slider-pivot-move'
          ).outerHeight()

          $(
            '#luckysheet-modal-dialog-pivotTable-list, #luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value'
          ).css('cursor', 'default')
        }
      )

      $(
        '#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value'
      )
        .mousemove(function(e) {
          if (_this.movestate) {
            if (_this.moveitemposition.length == 0) {
              _this.moveitemposition = [0]

              $(this)
                .find('.luckysheet-modal-dialog-slider-config-item')
                .each(function(i) {
                  let $t = $(this),
                    h = $t.outerHeight()
                  _this.moveitemposition.push(_this.moveitemposition[i] + h + 2)
                })
              $(this).append(
                '<div id="luckysheet-modal-dialog-config-order-help" style="position:absolute;height:3px;width:100%;background:#007ACC;z-index:3000;pointer-events: none;user-select:none;"></div>'
              )
            }

            $('#luckysheet-modal-dialog-slider-pivot-move').css({
              background: '#FD8585',
              color: '#fff',
              border: '1px solid #FD7070',
            })
            let x = event.pageX,
              y = event.pageY,
              $container = $(this)
            let curtop = y - $container.offset().top + $container.scrollTop()
            let position = _this.moveitemposition
            let row_index = luckysheet_searcharray(position, curtop)

            if (row_index == -1) {
              $('#luckysheet-modal-dialog-config-order-help').css({
                top: position[position.length - 1],
              })
            } else if (
              curtop - position[row_index - 1] >
              (position[row_index] - position[row_index - 1]) / 2
            ) {
              $('#luckysheet-modal-dialog-config-order-help').css({
                top: position[row_index],
              })
            } else {
              $('#luckysheet-modal-dialog-config-order-help').css({
                top: position[row_index - 1],
              })
            }
          }
        })
        .mouseleave(function() {
          if (_this.movestate) {
            $('#luckysheet-modal-dialog-slider-pivot-move').css({
              background: '#fff',
              color: '#000',
              border: '1px dotted #000',
            })
            _this.moveitemposition = []
            $('#luckysheet-modal-dialog-config-order-help').remove()
          }
        })
        .mouseup(function(e) {
          if (_this.movestate) {
            let $t = $(this)
            let itemHTML

            if (_this.movesave.containerid == $t.attr('id')) {
              itemHTML = _this.movesave.obj.clone()
            } else {
              let name = _this.movesave.name,
                sumtype = '',
                nameindex = ''

              if ($t.attr('id') == 'luckysheet-modal-dialog-config-value') {
                let type =
                  _this.pivot_data_type[_this.movesave.index.toString()]

                if (type == 'num') {
                  name = locale_pivotTable.valueStatisticsSUM + ':' + name
                  sumtype = "data-sumtype='SUM'"
                  nameindex = "data-nameindex='0'"
                } else {
                  name = locale_pivotTable.valueStatisticsCOUNTA + ':' + name
                  sumtype = "data-sumtype='COUNTA'"
                  nameindex = "data-nameindex='0'"
                }

                $('#luckysheet-modal-dialog-config-value')
                  .find('.luckysheet-modal-dialog-slider-config-item')
                  .each(function() {
                    if (
                      $(this)
                        .find('.luckysheet-modal-dialog-slider-config-item-txt')
                        .text() == name
                    ) {
                      let ni = parseFloat($(this).data('nameindex')) + 1
                      name = name + ni.toString()
                      $(this).data('nameindex', ni)
                      return false
                    }
                  })
              }

              itemHTML =
                '<div title="' +
                name +
                '" class="luckysheet-modal-dialog-slider-config-item" ' +
                nameindex +
                ' ' +
                sumtype +
                ' data-index="' +
                _this.movesave.index +
                '" data-name="' +
                _this.movesave.name +
                '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' +
                nameindex +
                ' ' +
                sumtype +
                ' data-index="' +
                _this.movesave.index +
                '" data-name="' +
                _this.movesave.name +
                '">' +
                name +
                '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'
            }

            let x = event.pageX,
              y = event.pageY,
              $container = $(this)
            let curtop = y - $container.offset().top + $container.scrollTop()
            let position = _this.moveitemposition
            let row_index = luckysheet_searcharray(position, curtop)

            if (
              _this.movesave.containerid ==
                'luckysheet-modal-dialog-pivotTable-list' ||
              (_this.movesave.containerid ==
                'luckysheet-modal-dialog-config-value' &&
                _this.movesave.containerid != $t.attr('id'))
            ) {
              $(
                '#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column'
              )
                .find('.luckysheet-modal-dialog-slider-config-item')
                .each(function() {
                  if ($(this).data('index') == _this.movesave.index) {
                    $(this).remove()
                  }
                })
            }

            if (row_index == -1) {
              if (
                $t.find('.luckysheet-modal-dialog-slider-config-item').length ==
                0
              ) {
                $t.append(itemHTML)
              } else {
                $t.find('.luckysheet-modal-dialog-slider-config-item')
                  .last()
                  .after(itemHTML)
              }
            } else if (
              curtop - position[row_index - 1] >
              (position[row_index] - position[row_index - 1]) / 2
            ) {
              $t.find('.luckysheet-modal-dialog-slider-config-item')
                .eq(row_index - 1)
                .after(itemHTML)
            } else {
              $t.find('.luckysheet-modal-dialog-slider-config-item')
                .eq(row_index - 1)
                .before(itemHTML)
            }

            if (
              _this.movesave.containerid ==
              'luckysheet-modal-dialog-pivotTable-list'
            ) {
            } else if (
              _this.movesave.containerid ==
                'luckysheet-modal-dialog-config-value' &&
              _this.movesave.containerid != $t.attr('id')
            ) {
            } else {
              _this.movesave.obj.remove()
            }

            $('#luckysheet-modal-dialog-pivotTable-list')
              .find('.luckysheet-modal-dialog-slider-list-item')
              .each(function() {
                let $seleted = $(this).find(
                  '.luckysheet-slider-list-item-selected'
                )
                if (
                  $(this).data('index') == _this.movesave.index &&
                  $seleted.find('i').length == 0
                ) {
                  $seleted.append(
                    '<i class="fa fa-check luckysheet-mousedown-cancel"></i>'
                  )
                }
              })

            _this.refreshPivotTable()

            $('#luckysheet-modal-dialog-slider-pivot-move').remove()
            _this.movestate = false
            $(
              '#luckysheet-modal-dialog-pivotTable-list, #luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value'
            ).css('cursor', 'default')
            _this.moveitemposition = []
            $('#luckysheet-modal-dialog-config-order-help').remove()
            _this.showvaluecolrow()
            e.stopPropagation()
          }
        })

      $('#luckysheet-modal-dialog-pivotTable-list').on(
        'click',
        '.luckysheet-slider-list-item-selected',
        function() {
          let $t = $(this),
            $item = $t.parent(),
            index = $item.data('index'),
            name = $item.data('name')

          if ($t.find('i').length == 0) {
            $t.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>')

            let type = _this.pivot_data_type[index.toString()],
              itemHTML

            if (type == 'num') {
              itemHTML =
                '<div title="' +
                name +
                '" class="luckysheet-modal-dialog-slider-config-item" data-nameindex="0" data-sumtype="SUM" data-index="' +
                index +
                '" data-name="' +
                name +
                '"><div class="luckysheet-modal-dialog-slider-config-item-txt" data-nameindex="0" data-sumtype="SUM" data-index="' +
                index +
                '" data-name="' +
                name +
                '">求和:' +
                name +
                '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'
              $('#luckysheet-modal-dialog-config-value').append(itemHTML)
            } else {
              itemHTML =
                '<div title="' +
                name +
                '" class="luckysheet-modal-dialog-slider-config-item" data-index="' +
                index +
                '" data-name="' +
                name +
                '"><div class="luckysheet-modal-dialog-slider-config-item-txt" data-index="' +
                index +
                '" data-name="' +
                name +
                '">' +
                name +
                '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'

              let $column = $('#luckysheet-modal-dialog-config-column'),
                $row = $('#luckysheet-modal-dialog-config-row')
              let columnitem = $column.find(
                  '.luckysheet-modal-dialog-slider-config-item'
                ),
                rowitem = $row.find(
                  '.luckysheet-modal-dialog-slider-config-item'
                )

              if (columnitem.length < 2) {
                $column.append(itemHTML)
              } else if (rowitem.length < 2) {
                $row.append(itemHTML)
              } else {
                $column.append(itemHTML)
              }
            }
          } else {
            $t.find('i').remove()
            $(
              '#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value'
            )
              .find('.luckysheet-modal-dialog-slider-config-item')
              .each(function() {
                if ($(this).data('index') == index) {
                  if (
                    $(this)
                      .parent()
                      .attr('id') == 'luckysheet-modal-dialog-config-value'
                  ) {
                    _this.resetOrderby($(this))
                  }
                  $(this).remove()
                }
              })
          }

          _this.refreshPivotTable()
          _this.showvaluecolrow()
        }
      )

      $('#luckysheet-dialog-pivotTable-clearitem').click(function() {
        $(
          '#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value'
        )
          .find('.luckysheet-modal-dialog-slider-config-item')
          .each(function() {
            $(this).remove()
          })

        $('#luckysheet-modal-dialog-pivotTable-list')
          .find('.luckysheet-modal-dialog-slider-list-item')
          .each(function() {
            $(this)
              .find('.luckysheet-slider-list-item-selected')
              .find('i')
              .remove()
          })

        _this.refreshPivotTable()
        _this.showvaluecolrow()
      })
    }

    if (restore == null) {
      restore = false
    }

    if (_this.celldata.length <= 1 && _this.celldata[0].length <= 1) {
      if (isEditMode()) {
        alert(locale_pivotTable.errorIncreaseRange)
      } else {
        tooltip.info('', locale_pivotTable.errorIncreaseRange)
      }
    }

    let selecteditem = '',
      selecteditemIndex = 1,
      selecteditemtest = {},
      selecteditemNullIndex = 1

    for (let i = 0; i < _this.celldata[0].length; i++) {
      let name
      if (!!_this.celldata[0][i] && !!_this.celldata[0][i]['m']) {
        name = _this.celldata[0][i]['m']
      } else {
        name = getcellvalue(0, i, _this.celldata)
      }

      if (name != null) {
        name = name.toString()
      }

      if (name == null || $.trim(name.toString()).length == 0) {
        name = locale_pivotTable.titleColumn + ' ' + selecteditemNullIndex
      }
      selecteditemNullIndex++

      if (name in selecteditemtest) {
        name = name + selecteditemIndex++
        if (name in selecteditemtest) {
          name = name + selecteditemIndex++
          if (name in selecteditemtest) {
            name = name + selecteditemIndex++
          }
        }
      }
      selecteditemtest[name] = 1

      let dataother = '',
        style = ''

      if (restore && _this.filterparm != null) {
        if (_this.filterparm[i.toString()] != null) {
          let itemset = _this.filterparm[i.toString()]
          if (itemset.rowhidden != null) {
            dataother +=
              "data-rowhidden='" + JSON.stringify(itemset.rowhidden) + "'"
          }

          if (itemset.selected != null) {
            dataother +=
              "data-selected='" + JSON.stringify(itemset.selected) + "'"
          }

          if (itemset.caljs != null) {
            let caljsset = itemset.caljs
            if (caljsset.value != null) {
              dataother += "data-byconditionvalue='" + caljsset.value + "'"
            }

            if (caljsset.type != null) {
              dataother += "data-byconditiontype='" + caljsset.type + "'"
            }

            if (caljsset.text != null) {
              dataother += "data-byconditiontext='" + caljsset.text + "'"
            }

            if (caljsset.value1 != null) {
              dataother += "data-byconditionvalue1='" + caljsset.value1 + "'"
            }

            if (caljsset.value2 != null) {
              dataother += "data-byconditionvalue2='" + caljsset.value2 + "'"
            }
          }
        }
      }

      if (dataother.length > 0) {
        style = 'display:block;'
      }

      selecteditem +=
        '<div class="luckysheet-modal-dialog-slider-list-item" ' +
        dataother +
        ' data-index="' +
        i +
        '" data-name="' +
        name +
        '"><div title="' +
        locale_pivotTable.titleAddColumn +
        '" class="luckysheet-slider-list-item-selected"><div></div></div><div title="' +
        locale_pivotTable.titleMoveColumn +
        '" class="luckysheet-slider-list-item-name" ' +
        dataother +
        ' data-index="' +
        i +
        '" data-name="' +
        name +
        '">' +
        name +
        '</div><div title="' +
        locale_pivotTable.titleClearColumnFilter +
        '" class="luckysheet-slider-list-item-filtered" style="' +
        style +
        '"><i class="fa fa-filter luckysheet-mousedown-cancel" aria-hidden="true"></i><i class="lksticonfont luckysheet-iconfont-qingchu" aria-hidden="true"></i></div><div title="' +
        locale_pivotTable.titleFilterColumn +
        '" class="luckysheet-slider-list-item-filter"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'
    }
    $('#luckysheet-modal-dialog-pivotTable-list').html(selecteditem)

    $('#luckysheetpivottablevaluecolrowshow').hide()
    $('#luckysheetpivottablevaluecolrow').prop('checked', true)
    $('#luckysheetpivottablevaluecolrow1').prop('checked', false)

    $(
      '#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value'
    ).empty()

    if (restore) {
      if (_this.filter != null && _this.filter.length > 0) {
        for (let i = 0; i < _this.filter.length; i++) {
          let item = _this.filter[i]

          let itemHTML =
            '<div title="' +
            name +
            '" class="luckysheet-modal-dialog-slider-config-item" data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '"><div class="luckysheet-modal-dialog-slider-config-item-txt" data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '">' +
            item.name +
            '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'

          $('#luckysheet-modal-dialog-config-filter').append(itemHTML)

          let $seleted = $(
            '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
          )
            .eq(item.index)
            .find('.luckysheet-slider-list-item-selected')
          if ($seleted.find('i').length == 0) {
            $seleted.append(
              '<i class="fa fa-check luckysheet-mousedown-cancel"></i>'
            )
          }
        }
      }

      if (_this.row != null && _this.row.length > 0) {
        for (let i = 0; i < _this.row.length; i++) {
          let item = _this.row[i]
          let otherset = ''

          if (item.order != null) {
            otherset += "data-order = '" + item.order + "'"
          }

          if (item.orderby != null) {
            otherset += "data-orderby = '" + item.orderby + "'"
          }

          if (item.order != null) {
            otherset += "data-stastic = '" + item.stastic + "'"
          }

          let itemHTML =
            '<div title="' +
            name +
            '" class="luckysheet-modal-dialog-slider-config-item" ' +
            otherset +
            ' data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' +
            otherset +
            ' data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '">' +
            item.name +
            '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'

          $('#luckysheet-modal-dialog-config-row').append(itemHTML)

          let $seleted = $(
            '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
          )
            .eq(item.index)
            .find('.luckysheet-slider-list-item-selected')
          if ($seleted.find('i').length == 0) {
            $seleted.append(
              '<i class="fa fa-check luckysheet-mousedown-cancel"></i>'
            )
          }
        }
      }

      if (_this.column != null && _this.column.length > 0) {
        for (let i = 0; i < _this.column.length; i++) {
          let item = _this.column[i]
          let otherset = ''

          if (item.order != null) {
            otherset += "data-order = '" + item.order + "'"
          }

          if (item.orderby != null) {
            otherset += "data-orderby = '" + item.orderby + "'"
          }

          if (item.order != null) {
            otherset += "data-stastic = '" + item.stastic + "'"
          }

          let itemHTML =
            '<div title="' +
            name +
            '" class="luckysheet-modal-dialog-slider-config-item" ' +
            otherset +
            ' data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' +
            otherset +
            ' data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '">' +
            item.name +
            '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'

          $('#luckysheet-modal-dialog-config-column').append(itemHTML)

          let $seleted = $(
            '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
          )
            .eq(item.index)
            .find('.luckysheet-slider-list-item-selected')
          if ($seleted.find('i').length == 0) {
            $seleted.append(
              '<i class="fa fa-check luckysheet-mousedown-cancel"></i>'
            )
          }
        }
      }

      if (_this.values != null && _this.values.length > 0) {
        for (let i = 0; i < _this.values.length; i++) {
          let item = _this.values[i]
          let otherset = ''

          if (item.sumtype != null) {
            otherset += "data-sumtype = '" + item.sumtype + "'"
          }

          if (item.nameindex != null) {
            otherset += "data-nameindex = '" + item.nameindex + "'"
          }

          let itemHTML =
            '<div title="' +
            name +
            '" class="luckysheet-modal-dialog-slider-config-item" ' +
            otherset +
            ' data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' +
            otherset +
            ' data-index="' +
            item.index +
            '" data-name="' +
            item.name +
            '">' +
            _this.getSumTypeName(item.sumtype) +
            ':' +
            item.name +
            '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>'

          $('#luckysheet-modal-dialog-config-value').append(itemHTML)

          let $seleted = $(
            '#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item'
          )
            .eq(item.index)
            .find('.luckysheet-slider-list-item-selected')
          if ($seleted.find('i').length == 0) {
            $seleted.append(
              '<i class="fa fa-check luckysheet-mousedown-cancel"></i>'
            )
          }
        }

        if (_this.values.length >= 2) {
          $('#luckysheetpivottablevaluecolrowshow').show()
          if (_this.showType == 'column') {
            $('#luckysheetpivottablevaluecolrow').prop('checked', true)
            $(
              "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']"
            ).addClass('ui-state-active')

            $('#luckysheetpivottablevaluecolrow1').prop('checked', false)
            $(
              "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']"
            ).removeClass('ui-state-active')
          } else {
            $('#luckysheetpivottablevaluecolrow1').prop('checked', true)
            $(
              "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']"
            ).addClass('ui-state-active')

            $('#luckysheetpivottablevaluecolrow').prop('checked', false)
            $(
              "#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']"
            ).removeClass('ui-state-active')
          }
        }
      }
    }

    $('#luckysheet-dialog-pivotTable-range').html(
      getRangetxt(_this.pivotDataSheetIndex, _this.pivot_select_save)
    )
    $('#luckysheet-modal-dialog-slider-pivot').show()

    luckysheetsizeauto(false)
  },
  getComposeArray: function(data) {
    if (data.length == 0) {
      return []
    }

    let ret = []
    for (let i = 0; i < data.length; i++) {
      let name = ''
      for (let x = 0; x <= i; x++) {
        if (!!data[x] && !!data[x]['m']) {
          name += data[x]['m']
        } else {
          name += getcellvalue(x, null, data)
        }
      }

      ret.push(name)
    }

    return ret
  },
  getnameArray: function(data, field) {
    if (data.length == 0) {
      return []
    }

    if (field.length == 0) {
      return []
    }

    let ret = []
    for (let i = 0; i < field.length; i++) {
      let c_value
      if (!!data[field[i].index] && !!data[field[i].index]['m']) {
        c_value = data[field[i].index]['m']
      } else {
        c_value = getcellvalue(field[i].index, null, data)
      }

      ret.push(c_value)
    }

    return ret
  },
  getTitleFromGroup: function(group, config, dataposition) {
    let _this = this
    let orderbygroup = _this.orderbygroup(group, config, dataposition)

    return _this.generategrouparraymain(orderbygroup, config)
  },
  orderbygroup: function(group, config, dataposition) {
    let _this = this

    let stackset = []
    if (group.length == 0) {
      return []
    }
    stackset = group

    let d = null,
      alllength = stackset.length,
      alllengthInital = stackset.length,
      a = 0

    while (alllength != 0) {
      d = stackset[a++]
      alllength--

      if (d.children != null && d.children.length > 0) {
        d.children = _this.orderbygroupchildren(
          d.children,
          config[d.index].orderby,
          config[d.index].order,
          dataposition
        )

        for (let i = 0; i < d.children.length; i++) {
          stackset.push(d.children[i])
          alllength++
        }
      }
    }

    return group.splice(0, alllengthInital)
  },
  orderbygroupchildren: function(childrens, orderby, order, dataposition) {
    if (childrens.length == 0) {
      return []
    }

    let isAsc = false
    if (order == null || order == 'asc') {
      isAsc = true
    }

    const _locale = locale()
    const locale_filter = _locale.filter

    let a = function(x, y) {
      let f = null,
        s = null

      if (orderby == 'self' || orderby == null) {
        if (x.name == null) {
          f = locale_filter.valueBlank
        } else {
          f = x.name.toString()
        }

        if (y.name == null) {
          s = locale_filter.valueBlank
        } else {
          s = y.name.toString()
        }

        if (isdatetime(f) && isdatetime(s)) {
          return diff(f, s)
        }
      } else {
        f = parseFloat(dataposition[x.orderby].result)
        s = parseFloat(dataposition[y.orderby].result)
      }

      if (!isNaN(f) && !isNaN(s)) {
        return numeral(f).value() - numeral(s).value()
      } else if (isNaN(f) && isNaN(s)) {
        return f.localeCompare(s)
      } else if (isNaN(f)) {
        return 1
      } else if (isNaN(s)) {
        return -1
      }
    }

    let d = function(x, y) {
      let f = null,
        s = null

      if (orderby == 'self' || orderby == null) {
        if (x.name == null) {
          f = locale_filter.valueBlank
        } else {
          f = x.name.toString()
        }

        if (y.name == null) {
          s = locale_filter.valueBlank
        } else {
          s = y.name.toString()
        }

        if (isdatetime(f) && isdatetime(s)) {
          return diff(f, s)
        }
      } else {
        f = parseFloat(dataposition[x.orderby].result)
        s = parseFloat(dataposition[y.orderby].result)
      }

      if (!isNaN(f) && !isNaN(s)) {
        return numeral(s).value() - numeral(f).value()
      } else if (isNaN(f) && isNaN(s)) {
        return s.localeCompare(f)
      } else if (isNaN(f)) {
        return -1
      } else if (isNaN(s)) {
        return 1
      }
    }

    if (isAsc) {
      return childrens.sort(a)
    } else {
      return childrens.sort(d)
    }
  },
  generategroupaddstatic: function(arr, name) {
    let stasticarr = []
    const _locale = locale()
    const locale_pivotTable = _locale.pivotTable
    for (let a = 0; a < arr[0].length; a++) {
      if (a == 0) {
        if (name == locale_pivotTable.valueSum) {
          stasticarr.push(name)
        } else {
          stasticarr.push({ name: name, issum: true })
        }
      } else {
        stasticarr.push('')
      }
    }

    return stasticarr
  },
  generategrouparraymain: function(group, config) {
    let _this = this

    //生成数组
    let ret = []
    for (let i = 0; i < group.length; i++) {
      let name = group[i].name
      let arr = _this.generategrouparray(group[i].children, config, 1)

      if (config[0].stastic == '1' || config[0].stastic == null) {
        arr.push(_this.generategroupaddstatic(arr, name))
      }

      ret = ret.concat(arr)
    }

    return ret
  },
  generategrouparray: function(group, config, level) {
    let _this = this

    let ret = []
    for (let i = 0; i < group.length; i++) {
      let name = group[i].name
      let arr

      if (group[i].children == 0 || group[i].children.length == 0) {
        arr = [name]
        ret.push(arr)
      } else {
        arr = _this.generategrouparray(group[i].children, config, level + 1)

        for (let a = 0; a < arr.length; a++) {
          arr[a].unshift(name)
        }

        if (config[level].stastic == '1' || config[level].stastic == null) {
          arr.push(_this.generategroupaddstatic(arr, name))
        }

        ret = ret.concat(arr)
      }
    }

    return ret
  },
  addStatisticsData: function(dataposition, valueobj, indicator, d_value) {
    if (dataposition[indicator] == null) {
      dataposition[indicator] = {
        data: [],
        count: 0,
        max: -Infinity,
        min: Infinity,
        counta: 0,
        countunique: 0,
        countuniquedata: {},
        sum: 0,
        digitaldata: [],
        sumtype: valueobj.sumtype,
        index: valueobj.index,
        name: valueobj.fullname,
        acc: 0,
      }
    }

    if (isdatatypemulti(d_value)['num'] === true) {
      //fix issue 265
      let num = numFormat(d_value, 6)
      dataposition[indicator]['digitaldata'].push(num)
      dataposition[indicator]['count'] += 1
      dataposition[indicator]['sum'] += num

      if (num > dataposition[indicator]['max']) {
        dataposition[indicator]['max'] = num
      }

      if (num < dataposition[indicator]['min']) {
        dataposition[indicator]['min'] = num
      }

      let newAcc = numfloatlen(num)

      if (newAcc > dataposition[indicator]['acc']) {
        dataposition[indicator]['acc'] = newAcc
      }
    }

    if (d_value != '') {
      dataposition[indicator]['data'].push(d_value)
      dataposition[indicator]['counta'] += 1
      if (!(d_value in dataposition[indicator]['countuniquedata'])) {
        dataposition[indicator]['countuniquedata'][d_value] = 1
        dataposition[indicator]['countunique'] += 1
      }
    }
  },
  dataHandler: function(column, row, values, showType, celldata) {
    //column:[{"index":1, name:"列1", "order":"asc", "orderby":"self/0/1/2", "stastic":"0/1"}]
    //row:[{"index":1, name:"列3", "order":"asc", "orderby":"self/0/1/2", "stastic":"0/1"}]
    //values:[{"index":1, "sumtype":"SUM/COUNT/COUNTA/COUNTUNIQUE/AVERAGE/MAX/MIN/MEDIAN/PRODUCT/STDEV/STDEVP/let/VARP", "name":"求和:fyc"}]
    let _this = this

    const _locale = locale()
    const locale_filter = _locale.filter
    const locale_pivotTable = _locale.pivotTable

    if (showType == null) {
      showType = 'column'
    }

    if (
      (column.length == 0 && row.length == 0 && values.length == 0) ||
      celldata.length == 0
    ) {
      _this.pivotDatas = []
      return []
    }

    //生成透视表值及定位
    let dataposition = {},
      data = celldata,
      datarowtitle = [],
      datarowtitlegroup = [],
      datarowposition = {},
      datarowposition_i = 0,
      datacoltitle = [],
      datacoltitlegroup = [],
      datacolposition = {},
      datacolposition_i = 0

    for (let i = 1; i < data.length; i++) {
      let d = data[i]
      let groupbyrowtxt = '',
        groupbycoltxt = '',
        rowtxt = '',
        rowtitle = [],
        rowtitlename = [],
        coltxt = '',
        coltitle = [],
        coltitlename = []

      //["四川", "成都", "邛崃"] 转换为 ["四川", "四川成都", "四川成都邛崃"]
      rowtitlename = _this.getnameArray(d, row)
      coltitlename = _this.getnameArray(d, column)

      rowtitle = _this.getComposeArray(rowtitlename)
      coltitle = _this.getComposeArray(coltitlename)

      if (rowtitle.length > 0) {
        rowtitle.unshift(locale_pivotTable.valueSum)
      }

      if (coltitle.length > 0) {
        coltitle.unshift(locale_pivotTable.valueSum)
      }

      let curentLevelobj_row = datarowposition,
        curentLevelarr_row = datarowtitlegroup

      for (let r = 0; r < rowtitle.length; r++) {
        let item = rowtitle[r],
          name = r == 0 ? locale_pivotTable.valueSum : rowtitlename[r - 1] //修改

        if (
          curentLevelobj_row[r.toString()] != null &&
          curentLevelobj_row[r.toString()][item] != null
        ) {
          //修改
          curentLevelarr_row =
            curentLevelarr_row[curentLevelobj_row[r.toString()][item]].children
        } else {
          let orderby =
            r == 0
              ? 'self'
              : row[r - 1].orderby == 'self' || row[r - 1].orderby == null
              ? item
              : showType == 'column'
              ? item + values[parseInt(row[r - 1].orderby)].fullname
              : item + locale_pivotTable.valueSum

          if (name == null) {
            name = locale_filter.valueBlank
          }

          curentLevelarr_row.push({
            name: name,
            fullname: item,
            index: r,
            orderby: orderby,
            children: [],
          })

          if (curentLevelobj_row[r.toString()] == null) {
            curentLevelobj_row[r.toString()] = {}
          }

          if (curentLevelobj_row[r.toString()][item] == null) {
            curentLevelobj_row[r.toString()][item] =
              curentLevelarr_row.length - 1
          }

          curentLevelarr_row =
            curentLevelarr_row[curentLevelarr_row.length - 1].children
        }
      }

      let curentLevelobj_col = datacolposition,
        curentLevelarr_col = datacoltitlegroup

      for (let r = 0; r < coltitle.length; r++) {
        let item = coltitle[r],
          name = r == 0 ? locale_pivotTable.valueSum : coltitlename[r - 1]

        if (
          curentLevelobj_col[r.toString()] != null &&
          curentLevelobj_col[r.toString()][item] != null
        ) {
          curentLevelarr_col =
            curentLevelarr_col[curentLevelobj_col[r.toString()][item]].children
        } else {
          let orderby =
            r == 0
              ? 'self'
              : column[r - 1].orderby == 'self' || column[r - 1].orderby == null
              ? item
              : showType == 'column'
              ? locale_pivotTable.valueSum + item
              : values[parseInt(column[r - 1].orderby)].fullname + item

          if (name == null) {
            name = locale_filter.valueBlank
          }

          curentLevelarr_col.push({
            name: name,
            fullname: item,
            index: r,
            orderby: orderby,
            children: [],
          })

          if (curentLevelobj_col[r.toString()] == null) {
            curentLevelobj_col[r.toString()] = {}
          }

          if (curentLevelobj_col[r.toString()][item] == null) {
            curentLevelobj_col[r.toString()][item] =
              curentLevelarr_col.length - 1
          }

          curentLevelarr_col =
            curentLevelarr_col[curentLevelarr_col.length - 1].children
        }
      }

      let v_str = ''
      for (let v = 0; v < values.length; v++) {
        let d_value = getcellvalue(values[v].index, null, d)

        let coltitle_c = [].concat(coltitle),
          rowtitle_c = [].concat(rowtitle)
        if (showType == 'column') {
          if (coltitle_c.length > 0) {
            coltitle_c.push('')
            coltitle_c = coltitle_c
              .join(values[v].fullname + '|||')
              .split('|||')
              .slice(0, coltitle_c.length - 1)
          } else {
            coltitle_c.push(values[v].fullname)
          }
        } else {
          if (rowtitle_c.length > 0) {
            rowtitle_c.push('')
            rowtitle_c = rowtitle_c
              .join(values[v].fullname + '|||')
              .split('|||')
              .slice(0, rowtitle_c.length - 1)
          } else {
            rowtitle_c.push(values[v].fullname)
          }
        }

        if (coltitle_c.length == 0) {
          coltitle_c.push('')
        }

        if (rowtitle_c.length == 0) {
          rowtitle_c.push('')
        }

        for (let r = 0; r < rowtitle_c.length; r++) {
          for (let c = 0; c < coltitle_c.length; c++) {
            let indicator = rowtitle_c[r] + coltitle_c[c]
            _this.addStatisticsData(dataposition, values[v], indicator, d_value)
          }
        }
      }
    }

    //计算值列
    //SUM/COUNT/COUNTA/COUNTUNIQUE/AVERAGE/MAX/MIN/MEDIAN/PRODUCT/STDEV/STDEVP/let/VARP
    for (let indicator in dataposition) {
      let json = dataposition[indicator]

      if (json.sumtype == 'SUM') {
        json.result = json.sum
      } else if (json.sumtype == 'COUNT') {
        json.result = json.count
      } else if (json.sumtype == 'COUNTA') {
        json.result = json.counta
      } else if (json.sumtype == 'COUNTUNIQUE') {
        json.result = json.countunique
      } else if (json.sumtype == 'AVERAGE') {
        json.result = numFormat(json.sum / json.count)
      } else if (json.sumtype == 'MAX') {
        json.result = json.max
      } else if (json.sumtype == 'MIN') {
        json.result = json.min
      } else if (json.sumtype == 'MEDIAN') {
        let numArr = json.digitaldata.sort(function(a, b) {
          return a - b
        })
        let numLen = numArr.length
        let numindex = parseInt(numLen / 2)

        if (numLen % 2 == 0) {
          json.result = (numArr[numindex - 1] + numArr[numindex]) / 2
        } else {
          json.result = numArr[numindex]
        }
      } else if (json.sumtype == 'PRODUCT') {
        json.result = new Function('return ' + json.digitaldata.join('*'))()
      } else if (json.sumtype == 'STDEV') {
        let mean = json.sum / json.count
        json.result = analysis.STDEV(mean, json.digitaldata)
      } else if (json.sumtype == 'STDEVP') {
        let mean = json.sum / json.count
        json.result = analysis.STDEVP(mean, json.digitaldata)
      } else if (json.sumtype == 'let') {
        let mean = json.sum / json.count
        json.result = analysis.let(mean, json.digitaldata)
      } else if (json.sumtype == 'VARP') {
        let mean = json.sum / json.count
        json.result = analysis.VARP(mean, json.digitaldata)
      }

      let newAcc = numfloatlen(json.result)
      if (newAcc > json.acc) {
        json.acc = newAcc
      }

      json.result = numFormat(json.result, json.acc)
    }

    datarowtitle = _this.getTitleFromGroup(datarowtitlegroup, row, dataposition)
    datacoltitle = _this.getTitleFromGroup(
      datacoltitlegroup,
      column,
      dataposition
    )

    //加入值到列/行形成新的表头
    if (showType == 'column') {
      if (datacoltitle.length > 0 && datacoltitle[0].length > 0) {
        datacoltitle = _this.addValuesToTitle(datacoltitle, values)
      } else {
        for (let v = 0; v < values.length; v++) {
          datacoltitle.push([values[v].fullname])
        }
      }
    } else {
      if (datarowtitle.length > 0 && datarowtitle[0].length > 0) {
        datarowtitle = _this.addValuesToTitle(datarowtitle, values)
      } else {
        for (let v = 0; v < values.length; v++) {
          datarowtitle.push([values[v].fullname])
        }
      }
    }

    let datacoltitle_index = datacoltitle
    datacoltitle = luckysheetArray.transpose(datacoltitle, false)

    let valuenslen = values.length == 0 ? 0 : 1
    let rowLen =
        (datacoltitle.length == 0 ? valuenslen : datacoltitle.length) +
        (datarowtitle.length == 0 ? valuenslen : datarowtitle.length),
      colLen =
        (datacoltitle.length == 0 ? valuenslen : datacoltitle[0].length) +
        (datarowtitle.length == 0 ? valuenslen : datarowtitle[0].length)

    let rowOver = datacoltitle.length,
      colOver = datarowtitle.length == 0 ? 0 : datarowtitle[0].length

    let retdata = []
    for (let r = 0; r < rowLen; r++) {
      retdata[r] = new Array(colLen)

      for (let c = 0; c < colLen; c++) {
        let drt = datarowtitle[r - rowOver]

        if (r < rowOver && c < colOver) {
          //空白列头
          retdata[r][c] = ''
        } else if (r < rowOver && c >= colOver) {
          //列标题
          if (datacoltitle[r] != null) {
            if (getObjType(datacoltitle[r][c - colOver]) == 'object') {
              retdata[r][c] =
                datacoltitle[r][c - colOver].name + locale_pivotTable.valueSum
            } else {
              retdata[r][c] = datacoltitle[r][c - colOver]
            }
          } else {
            retdata[r][c] = ''
          }
        } else if (r >= rowOver && c < colOver) {
          //行标题
          if (drt != null) {
            if (getObjType(drt[c]) == 'object') {
              retdata[r][c] = drt[c].name + locale_pivotTable.valueSum
            } else {
              retdata[r][c] = drt[c]
            }
          } else {
            retdata[r][c] = ''
          }
        } else {
          //单元格内容
          let prefix = ''
          if (drt != null) {
            if (!(drt instanceof Array) || drt.length == 1) {
              if (drt instanceof Array) {
                prefix = drt[0]
              } else {
                prefix = drt
              }
            } else {
              for (let x = 0; x < drt.length; x++) {
                if (getObjType(drt[x]) == 'object') {
                  prefix += drt[x].name
                } else {
                  prefix += drt[x]
                }
              }
            }
          }

          let suffix = ''
          let dct = datacoltitle_index[c - colOver]
          if (dct != null) {
            if (!(dct instanceof Array) || dct.length == 1) {
              if (dct instanceof Array) {
                suffix = dct[0]
              } else {
                suffix = dct
              }
            } else {
              for (let x = 0; x < dct.length; x++) {
                if (getObjType(dct[x]) == 'object') {
                  suffix += dct[x].name
                } else {
                  suffix += dct[x]
                }
              }
            }
          }

          let indicator = prefix

          if (prefix != '' && suffix != '') {
            indicator = prefix + suffix
          } else if (prefix == '') {
            indicator = suffix
          }

          if (dataposition[indicator] == null) {
            retdata[r][c] = ''
          } else {
            retdata[r][c] = dataposition[indicator].result
          }
        }
      }
    }

    if (values.length == 1 && column.length > 0 && row.length > 0) {
      retdata[0][0] = values[0].fullname
      retdata.splice(column.length, 1)
    } else if (values.length == 1 && column.length > 0) {
      // 0: (6) ["English", "foreign language", "mathematics", "science", "Sum", undefined]
      // 1: (6) ["CountA:score", "CountA:score", "CountA:score", "CountA:score", "CountA:score", undefined]
      // 2: (6) [3, 3, 3, 3, 12, ""]
      //The above format does not meet viewing habits,Process retdata into the correct format
      let titleRow = retdata.splice(column.length, 1)
      let newRetdata = []
      for (let r = 0; r < retdata.length; r++) {
        let row = []
        if (r == retdata.length - 1) {
          row.push(titleRow[0][0])
        } else {
          row.push('')
        }
        for (let c = 0; c < retdata[r].length - 1; c++) {
          row.push(retdata[r][c])
        }
        newRetdata.push(row)
      }
      retdata = newRetdata
    }

    _this.pivotDatas = retdata

    return retdata
  },
  drillDown: function(row_index, col_index) {
    if (
      !checkProtectionAuthorityNormal(
        Store.currentSheetIndex,
        'usePivotTablereports'
      )
    ) {
      return
    }
    let _this = this

    let cell = _this.pivotDatas[row_index][col_index]
    let d = $.extend(true, [], sheetmanage.nulldata)

    const _locale = locale()
    const locale_filter = _locale.filter
    const locale_pivotTable = _locale.pivotTable

    let selecteditemNullIndex = 1
    for (let i = 0; i < _this.celldata[0].length; i++) {
      let name
      if (!!_this.celldata[0][i] && !!_this.celldata[0][i]['m']) {
        name = _this.celldata[0][i]['m']
      } else {
        name = getcellvalue(0, i, _this.celldata)
      }

      if (name != null) {
        name = name.toString()
      }

      if (name == null || $.trim(name.toString()).length == 0) {
        name = locale_pivotTable.titleColumn + ' ' + selecteditemNullIndex
      }
      selecteditemNullIndex++

      d[0][i] = name
    }

    let obj = {}

    //行
    if (_this.row != null && _this.row.length > 0) {
      for (let a = 0; a < _this.row.length; a++) {
        obj[_this.row[a]['index']] = _this.pivotDatas[row_index][a]
      }
    }

    //列
    if (_this.column != null && _this.column.length > 0) {
      for (let b = 0; b < _this.column.length; b++) {
        obj[_this.column[b]['index']] = _this.pivotDatas[b][col_index]
      }
    }

    let rowArr = []
    for (let j = 1; j < _this.celldata.length; j++) {
      let isEqual = true

      for (let x in obj) {
        let value
        if (!!_this.celldata[j][x] && !!_this.celldata[j][x]['m']) {
          value = _this.celldata[j][x]['m']
        } else {
          value = getcellvalue(j, x, _this.celldata)
        }

        if (value != null) {
          value = value.toString()
        } else {
          value = locale_filter.valueBlank
        }

        if (value != obj[x]) {
          isEqual = false
          break
        }
      }

      if (isEqual) {
        rowArr.push(j)
      }
    }

    for (let r = 0; r < rowArr.length; r++) {
      for (let c = 0; c < _this.celldata[0].length; c++) {
        let value
        if (
          !!_this.celldata[rowArr[r]][c] &&
          !!_this.celldata[rowArr[r]][c]['m']
        ) {
          value = _this.celldata[rowArr[r]][c]['m']
        } else {
          value = getcellvalue(rowArr[r], c, _this.celldata)
        }

        if (value != null) {
          value = value.toString()
        } else {
          value = ''
        }

        d[r + 1][c] = value
      }
    }

    Store.luckysheet_select_save = [
      { row: [0, rowArr.length], column: [0, _this.celldata[0].length - 1] },
    ]

    Store.clearjfundo = false
    jfrefreshgrid(d, Store.luckysheet_select_save)
    selectHightlightShow()
    Store.clearjfundo = true
  },
}

export default pivotTable
